Solved: unable to load recordset

[ibgreat]

I am new to VBA, but have been following a text. I am trying to set up an unbound form. I did the example in my text without a problem. When I try to apply it to the db I am working on the recordset does not appear to load. When I open the form I get an error msg stating, "Invalid sql statement..." I tried to switch the code to a formal sql statment:


.open "SELECT * FROM tblPerson-D", cnABCdb

but that didn't work either. I tried posting in another forum to no avail. I thought I would go to the experts...PLEASE HELP, it's been two days. Here is the code:



Option Compare Database
Option Explicit
Dim rsPerson As ADODB.Recordset
Dim cnABCdb As ADODB.Connection
Dim strConnection As String
Dim blnAddMode As Boolean

Private Sub Form_Load()
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentProject.Path & "\BehaviorDatabaseWorking.mdb;"
'create a new connection instance and open it using the connection string
Set cnABCdb = New ADODB.Connection
cnABCdb.Open strConnection
'create a new instance of a recordset
Set rsPerson = New ADODB.Recordset
'set various properties of the recordset
With rsPerson
'specify a cursortype and lock type that will allow updates
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
'open the recordset based on tblPerson-D table using the existing connection
.Open "tblPerson-D", cnABCdb
'disconnect the recordset
.ActiveConnection = Nothing
End With

[Mavyak]

Try putting square brackets around your table name.

[ibgreat]

Thanks, I actually had meant to try that but forgot. That seemed to work. I have to work through some other mistakes in the code and will let you know.

In the meantime, how come that made a difference in one db, but not in the other? I always wondered if there was specific times you are supposed to use the brackets. I assumed this was something to do with older code as my vba book doesn't seem to use them at all.

[Mavyak]

The square brackets are for Access. To VBA, they are just characters in a string variable. I'm not exactly positive when they are required either. My rule of thumb is, if the entire table/field name is made up of 0 through 9, a through z, or an underscore then I don't use them. Any characters outside those I just mentioned and I use them. I honed in on the hyphen in the table name and the first thing that came to my mind was, "I want some peanuts". Then I thought about the brackets.:rudolph:

[ibgreat]

:rotlaugh:

that's funny, thanks for the insight

[CreganTur]

This issue has to do with how finicky SQL is... it's the fussy-old-lady of programming languages; if everything isn't in its exact place, she's not happy :motz2:

When you're using SQL code in VBA you have to enclose table names and field names in brackets any time they have spaces in the name, or hyphens... there may be some other times this must take place, but I can't think of them.

As a part of good design it's suggested you should only use the underscore to separate parts of a name. Example:
Cust First Name -> should be CustFirstName Or Cust_First_Name... or any other iteration you can think of that works.

There are other functions in VBA (like DLookup) where you have to use brakcets no matter what.

HTH:thumb

[ibgreat]

Thanks, I will modify my code to reflect the underscore. Since there are conditions that always require brackets, is a better practice to always use them?

[CreganTur]

Thanks, I will modify my code to reflect the underscore.
I may have needed to be clearer- but this will involve changes the table name and the field names themselves. If your table is named 'tblPerson-D' then you can't just change your SQL code to 'tblPerson_D'- you'd have to change the table name to match.


Since there are conditions that always require brackets, is a better practice to always use them?
That's personal choice more than anything. I don't use the brackets unless the function/method I'm using forces me to. It's a few less keystrokes:type

[ibgreat]

Yes, I meant the table names too. Thanks for being more explicit, as someone trying to figure this out it is helpful at times.