Opening a recordset in VBA on an SQL srv table with Unique Identifier

[AmeriSource]

I am trying to open a recordset on a sql server table threw vba and it keeps telling me that i have to use the dbseechanges option. When i do use the dbseechanges option it still tells me that i have to use the dbseechanges option.

Thank you in advance,

Frank

 

[chiph]

Can you please post a representative sample of your code?
(Not 2000 lines worth, just the relevant portion please).

Chip H.

 

[AmeriSource]

This is a sample of what i'm trying to do:

set db=currentdb()
set rstInvoice=db.openrecordset("tblInvoice",,dbseeChanges)

*It's very simple the only thing is, is that tblInvoice has an autonumber id.

 

[balves]

Doesn't this mean you're going through Microsoft Access?

set db=currentdb()

 

[AmeriSource]

Yes, I am using Microsoft access to access this sql table.

 

[AmeriSource]

i have linked tables in my access database

 

[balves]

I think you would be better off using a connection string and going against the SQL Server database directly.

 

[joebickley]

hi m8

i have used this functionality a lot with linked tables and manipulating it with code

here is some of what i have had working b4 and that is with autonumbers
[tt]
Dim findcar As Recordset
Dim db4 As Database
Set db4 = currentdb()
Set findcar = db4.OpenRecordset("tblwhatever", , dbSeeChanges)
[/tt]
Do u get there error when u just make a conection ( like the above does) or when u try to change the data?

Joe

 

[AmeriSource]

when i make the connection, exactly like above

 

[Ryrie]

Did anyone ever help you with this? I was having the same exact problem. I found out that I had to also explicitly say it was a dynaset, too. Saying dbseechanges by itself didn't cut it. Try this:
Set rst = CurrentDb.OpenRecordset("mytable", dbOpenDynaset, dbSeeChanges)