ADO Reference (1 Viewer)

[khurram7x]

Hi,

Which Reference Library should I select for ADO commands please?? I'm using code below in reference the answer from the link:

https://access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

...and is there any advantage of using ADO (which i don't know anything about yet) vs DAO??

Thanks & Regards,
K

Dim adoCMD, adoRS, Conn As Object
Dim strSQL As String

Set Conn = CreateObject("ADODB.Connection")
Conn.Open ("DSN=****;Uid=****;Pwd=****;DATABASE=****")

strSQL = "SELECT * FROM tblBoQ_Progress WHERE BoQID = P1"

'Define attachment to database table specifics
Set adoCMD = CreateObject("ADODB.Command")
Set adoCMD.activeconnection = Conn

With adoCMD

.activeconnection = CurrentProject.Connection
.commandtype = accmdtext
.Parameters.Append .createParameter("P1", adVarChar, adParamInput, 25, Me.ID)
.commandtext = strSQL
Set adoRS = .Execute()

End With

 

[JHB]

What type do you think the two first variables are?

Dim adoCMD, adoRS, Conn As Object

 

[static]

Which Reference Library should I select for ADO commands please??

It won't really matter because you aren't using it.

Set Conn = CreateObject("ADODB.Connection")

createobject determines which library to load by checking your registry.
Adding a reference (and setting types for the variables) would only help by providing intellisense to your code.

...and is there any advantage of using ADO vs DAO??

Not in Access (unless you want to make forms bound to disconnected recordsets or something.) DAO is all built in and you rarely need to worry about connections, etc.
You would use ADO to connect dierectly to another type of database.

 

[khurram7x]

What type do you think the two first variables are?

Apparently, DAO, because i used it once in some code. More confusion, how would i define ADO object?

createobject determines which library to load by checking your registry.
Adding a reference (and setting types for the variables) would only help by providing intellisense to your code.

Thanks for explanation.

You would use ADO to connect dierectly to another type of database.

This what i'm trying to do. I'm connecting to back-end SQL Server and want to use ADO with Pass-Through queries. Though Pass-Through is also working with with DAO, wondering what is the advantage of ADO in this case?

Thanks

 

[Minty]

There is a good write up here http://www.utteraccess.com/forum/Dao-Ado-Latest-Viewpo-t1241488.html

And here http://www.utteraccess.com/wiki/index.php/Choosing_between_DAO_and_ADO

My take on it is that ADO is no longer the favourite of the MS development boys as it has nothing done to it since the introduction of Access 2007, but it does offer some advantages if you connect to other types of database other than SQL /Access.

 

[static]

It's really a question of ease of use and current/future requirements.

If you are just messing about to learn, or building a small utility then DAO will probably suffice.

If you are building a complex prototype that might be converted to some other dev environment in the future you might as well go with ADO.

The only real limitation to using DAO that I ever came across was its 255 field limit when trying to work with SQL tables containing ~1000.

 

[JHB]

Apparently, DAO,

No they are variant type, because you haven't declared them to a specific type.
If you want to declare them as DAO the use the below syntax.

  Dim dbs As [B][COLOR=Red]DAO.[/COLOR][/B]Database, rst As [B][COLOR=Red]DAO.[/COLOR][/B]Recordset

 

[khurram7x]

Seems like ADO and Pass-Through queries is a decent go if ADO is getting less attention. I also feel that to go through ADO is another learning path, but I do look to migrate some programs to .NET and instead put my efforts on learning .NET instead of ADO?
Thanks for suggestions and all the information/articles.

 

[khurram7x]

Ok, if i want to use my application in several different offices in various regions, each with its own back-end database. Each site will have a copy of MS Access front-end and SQL Server back-end with same database structure.


I am looking to dynamically set MS Access application in such a way that when it runs it dynamically replaces the appropriate DSN in Connect string using Temp Variable. This will help create variable for Pass-Through query function and call it every time according to available server.


Obviously Access is saving this settings somewhere or else it could not connect to the back-end database at start up?

 

[Minty]

You need DSN-Less connections I think - http://www.accessmvp.com/DJSteele/DSNLessLinks.html

 

[khurram7x]

Thanks Minty, yes i know about it but with this, as per our Commercial Director, software will be vulnerable enough to take on any computer and run easily without any additional setup (manually creating DSN connection) from Admin... that's why i'm stuck to use DSN unfortunately!

... another part of the puzzle is, even if i use DSN less connection, how I'm gonna get name of the back-end Server? To my knowledge Access application could only get local computer name with Environ$(...) function, but not back-end server.