Solved ADO connection (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 02:00
Joined
Jul 9, 2015
Messages
424
So I am playing around with ADO connections, and got this bit of code from a book: Microsoft Access 2013 Bible

Code:
Public Function ExecuteCommand()
    Dim adRs As ADODB.Recordset
    Dim adCmd As ADODB.Command
    
    Const sTABLE As String = "Users"
    
    Set adRs = New ADODB.Recordset
    Set adCmd = New ADODB.Command
    
    adCmd.ActiveConnection = CurrentProject.Connection
    adCmd.CommandText = sTABLE
    
    Set adRs = adCmd.Execute
    
    Debug.Print adRs.GetString
    
    adRs.Close
    Set adRs = Nothing
    Set adCmd = Nothing
    
End Function

However, I get an error: Invalid SQL statement, on the adCmd.Execute.

The book claims it works.

What am I missing?

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Hi. What exactly were you trying to achieve? Or expect to happen? If you're trying to open a recordset, you could try using the Open method.
 

mjdemaris

Working on it...
Local time
Today, 02:00
Joined
Jul 9, 2015
Messages
424
Apparently this code is supposed to populate a recordset based on the "Users" table. It's just an example from the book on using the Command object.

The goal is to ultimately find out what is causing my DB to get corrupted/become unrecognizable, so I am looking at moving some forms to unbound, rather than bound, and see if that makes any difference.

I may also move to using more temp tables, then running an update SQL statement or update using ADO/DAO.

At this point, I am still unsure whether ADO/DAO is more beneficial...and I may still need to look at transaction coding to prevent simultaneous updates/inserts/locks on the tables.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Apparently this code is supposed to populate a recordset based on the "Users" table. It's just an example from the book on using the Command object.

The goal is to ultimately find out what is causing my DB to get corrupted/become unrecognizable, so I am looking at moving some forms to unbound, rather than bound, and see if that makes any difference.

I may also move to using more temp tables, then running an update SQL statement or update using ADO/DAO.

At this point, I am still unsure whether ADO/DAO is more beneficial...and I may still need to look at transaction coding to prevent simultaneous updates/inserts/locks on the tables.
Hi. Thanks for the additional information. What are you using as your ADO provider? For example, are you executing this code in Access and the table is an Access table too?
 

mjdemaris

Working on it...
Local time
Today, 02:00
Joined
Jul 9, 2015
Messages
424
For example, are you executing this code in Access and the table is an Access table too?

Yes. Well, I just discovered that this will not work with a linked table, which "Users" is. It does work with a local table.

How would I change this to support a linked table? Would I need to change the connection string to connect the source DB?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
Yes. Well, I just discovered that this will not work with a linked table, which "Users" is. It does work with a local table.

How would I change this to support a linked table? Would I need to change the connection string to connect the source DB?
Hi. You didn't answer my question though. I just tried it in Access with a linked Access table and it worked without any errors.
 

mjdemaris

Working on it...
Local time
Today, 02:00
Joined
Jul 9, 2015
Messages
424
My answer was "Yes", and here's the provider info: Provider=Microsoft.ACE.OLEDB.12.0

So, a linked table worked for you...I feel stupid. I am using a test DB that I did not refresh the links in...

Anyway, a follow up question regarding connection properties:
How would you specify each property of the connection on separate lines?
For example:
Code:
adConn.open & _
     "Provider= Microsoft something" & _
     "Data Source = path to DB" & _
      "And whatever else I need for a connection"

I see a few options I am not familiar with when I print the ConnectionString, such as Mode, System Database, Registry path, Engine Type, etc...

Apologies for my oversight, and thank you for your patience!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:00
Joined
Oct 29, 2018
Messages
21,357
My answer was "Yes", and here's the provider info: Provider=Microsoft.ACE.OLEDB.12.0

So, a linked table worked for you...I feel stupid. I am using a test DB that I did not refresh the links in...

Anyway, a follow up question regarding connection properties:
How would you specify each property of the connection on separate lines?
For example:
Code:
adConn.open & _
     "Provider= Microsoft something" & _
     "Data Source = path to DB" & _
      "And whatever else I need for a connection"
I see a few options I am not familiar with when I print the ConnectionString, such as Mode, System Database, Registry path, Engine Type, etc...

Apologies for my oversight, and thank you for your patience!
Hi. The continuation characters (plural) in VBA are the underscore and space characters, so I think you're doing it correctly. The ampersand (&) is used to concatenate a string. As for connection object properties, you could take a look this article to see if it helps clarify some of them. Good luck!
 

Micron

AWF VIP
Local time
Today, 05:00
Joined
Oct 20, 2018
Messages
3,476
just as an fyi, do some research on ADO vs DAO and watch out for old posts. At one point M$ was going to stop developing DAO so I suspect many panicked and started developing in ADO. Guess what? M$ stopped pushing ADO as the cat's meow and no longer advocates that anyone migrate to it.
**Opinion: Unless you deal with a lot of large remote db's there's probably not much advantage to ADO and I find DAO easier to work with. I've also read that Sql Server no longer accepts OLEDB, which ADO uses, and you have to use ODBC or DAO instead. Just saying do your homework before you go down a particular road.
 
Last edited:

Users who are viewing this thread

Top Bottom