Problems opening an ADODB Recordset

[tfurnivall]

Hi

I'm building a lab environment into one of my projects. I'm testing the distribution of values over a largish number of attempts to create a unique value.
I have a table called LAB_UniqueIDTest, with two fields:
LAB_ID - the string value being tested, and
LAB_UsageCount - the number of times the value has been created.
I'm trying to open the table using the following code:

'   Initialize access to the LAB_UniqueIDTest table
Set rs = New ADODB.Recordset
rs.Open "LAB_UniqueIDTest", _
        CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic

I have similar snippets of code all over the application - either with a literal (as here) or as a string parameter.

I do not, ever, use the options parameter of rs.open (mainly because I can't translate the gibberish in the documentation into English), but these snippets work. Delightfully!

All except this one :-(
I get error message "Run time error -2147217900 (80040e14)
Invalid SQL statement - expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE

My guess is it's because somehow the Open procedure is trying to interpret the tablename as a SQL statement. But how come it isn't this obtuse at other times?

I know it's something I'm doing, but I can't figure it out.
Can anyone help? I'll blush unseen when I learn how I've screwed up!

Tony

 

[SOS]

Does that table have, as its primary key, a field that is not INT but is BIGINT? Access doesn't like BigInt.

 

[tfurnivall]

Thanks for the response, SOS

The table (LAB_UniqueIDTest) has just 2 fields:
LAB_ID which is a string, and
LAB_UsageCount which is a Long

The primary index is LAB_ID

The real issue is why, all of a sudden, the rs.Open that I use (about 50 times) in the application, doesn't work. Must be something blindingly obvious, that I just haven't thought of yet!

Tony

 

[Galaxiom]

My guess is it's because somehow the Open procedure is trying to interpret the tablename as a SQL statement. But how come it isn't this obtuse at other times?

Are you sure there is no spelling error?

Otherwise try specifying the CommandType

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675946(v=vs.85).aspx

BTW. The connection is better as:
CurrentProject.AccessConnection

This avoids updateability problems sometimes encountered with: Currentproject.Connection

 

[tfurnivall]

Well - close visual inspection, + Option Explicit and no compile errors lead me to believe that this is not an issue!

Wondering about why the other 99.9% of the time it works, are what led me to make my original post.

I'll stick with - It must be something blindingly obvious (except to the blind)

T

 

[Galaxiom]

Did you try my suggestion to specify the command type?

rs.Open "LAB_UniqueIDTest", _
        CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic, [B]adCmdTable[/B]

 

[tfurnivall]

Yes - it works.

But, of course, it does nothing to resolve my initial frustration! I did discover that there was a bug posted against ADODB Recordset opening this way when used against a MySQL database from VBScript. It's a ong way away from my situation, but it was sort of encouraging to learn!

Thanks Galaxiom - have a good evening!

Tony