Access 2000 Linked to SQL Server 2000, openRecordset error

[Philippa]

I'm trying to access data in vba using the openrecordset command. The
data in on a SQL Server 2000 database, and I have linked tables to
that data. the Table I'm trying to access is one of these linked
tables, and my codes is as follows:

Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)

when this line is executed I get the error:

"Run-time error '3622':

You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an Identity column. "

so I changed my code to:

Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)

When this is executed the following error is generated:

" Run-time error '13':

Type mismatch "

I noticed that it didn't seem to recognise the 'dbseechanges'
arguement as usual the case is correct for known expressions, yet the
's' was not captialised on the 'dbseeChanges' !!!!

Please help this is driving me potty...

[Terry Kreft]

Try

Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)

Assuming that vSQL is a valid SQL statement.

Terry

"Philippa" <ph*************@systemc.com> wrote in message
news:9c**************************@posting.google.c om...

I'm trying to access data in vba using the openrecordset command. The
data in on a SQL Server 2000 database, and I have linked tables to
that data. the Table I'm trying to access is one of these linked
tables, and my codes is as follows:

Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)

when this line is executed I get the error:

"Run-time error '3622':

You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an Identity column. "

so I changed my code to:

Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)

When this is executed the following error is generated:

" Run-time error '13':

Type mismatch "

I noticed that it didn't seem to recognise the 'dbseechanges'
arguement as usual the case is correct for known expressions, yet the
's' was not captialised on the 'dbseeChanges' !!!!

Please help this is driving me potty...

[Philippa]

Thanks for you responce, I tried what you suggested (yes vsql is a
valid SQL Statement shown below), and it still doesn't work, this time
I get the following error:

" Run-tiem error '3001':

Invalid Argument. "

vSQL = "SELECT * FROM tbl_DataCommentLog WHERE Comment_FileUID = '" &
pForm!Header_FileUID & "'"
pForm being a paramter passed into the sub using the ME in the forms
private sub event. Even when I change vSQL to be just a valid table
name e.g. "tbl_DataCommentLog" it returns the same error !

I have succesfull managed to use the openrecordset method but this was
when the table to a acces table not a linked one. I think the fact
the vb is not recognising the arguements 'dbOpenDynaset' and
'dbSeeChanges' ( I know this as usually the case of keywords is
corrected, and it wasn't for these words ), is relevant but I don't
understand why !!!!

My tables are linked via a SQL Server driver ODBC connection, is this
significant....?

Cheers Philippa

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<bn0ll6$9d0
$[e-mail]>...

Try

Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)

Assuming that vSQL is a valid SQL statement.

Terry

"Philippa" <ph*************@systemc.com> wrote in message
news:9c**************************@posting.google.c om...

I'm trying to access data in vba using the openrecordset command. The
data in on a SQL Server 2000 database, and I have linked tables to
that data. the Table I'm trying to access is one of these linked
tables, and my codes is as follows:

Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)

when this line is executed I get the error:

"Run-time error '3622':

You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an Identity column. "

so I changed my code to:

Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)

When this is executed the following error is generated:

" Run-time error '13':

Type mismatch "

I noticed that it didn't seem to recognise the 'dbseechanges'
arguement as usual the case is correct for known expressions, yet the
's' was not captialised on the 'dbseeChanges' !!!!

Please help this is driving me potty...

[Terry Kreft]

How is vRS declared?
Terry

"Philippa" <ph*************@systemc.com> wrote in message
news:9c*************************@posting.google.co m...

Thanks for you responce, I tried what you suggested (yes vsql is a
valid SQL Statement shown below), and it still doesn't work, this time
I get the following error:

" Run-tiem error '3001':

Invalid Argument. "

vSQL = "SELECT * FROM tbl_DataCommentLog WHERE Comment_FileUID = '" &
pForm!Header_FileUID & "'"
pForm being a paramter passed into the sub using the ME in the forms
private sub event. Even when I change vSQL to be just a valid table
name e.g. "tbl_DataCommentLog" it returns the same error !

I have succesfull managed to use the openrecordset method but this was
when the table to a acces table not a linked one. I think the fact
the vb is not recognising the arguements 'dbOpenDynaset' and
'dbSeeChanges' ( I know this as usually the case of keywords is
corrected, and it wasn't for these words ), is relevant but I don't
understand why !!!!

My tables are linked via a SQL Server driver ODBC connection, is this
significant....?

Cheers Philippa

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<bn0ll6$9d0
$[e-mail]>...

Try

Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)

Assuming that vSQL is a valid SQL statement.

Terry

"Philippa" <ph*************@systemc.com> wrote in message
news:9c**************************@posting.google.c om...

I'm trying to access data in vba using the openrecordset command. The
data in on a SQL Server 2000 database, and I have linked tables to
that data. the Table I'm trying to access is one of these linked
tables, and my codes is as follows:

Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)

when this line is executed I get the error:

"Run-time error '3622':

You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an Identity column. "

so I changed my code to:

Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)

When this is executed the following error is generated:

" Run-time error '13':

Type mismatch "

I noticed that it didn't seem to recognise the 'dbseechanges'
arguement as usual the case is correct for known expressions, yet the
's' was not captialised on the 'dbseeChanges' !!!!

Please help this is driving me potty...

[Philippa]

Dim vRS As Recordset

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<bn**********@newsreaderg1.core.theplanet.net >...

How is vRS declared?
Terry

"Philippa" <ph*************@systemc.com> wrote in message
news:9c*************************@posting.google.co m...

Thanks for you responce, I tried what you suggested (yes vsql is a
valid SQL Statement shown below), and it still doesn't work, this time
I get the following error:

" Run-tiem error '3001':

Invalid Argument. "

vSQL = "SELECT * FROM tbl_DataCommentLog WHERE Comment_FileUID = '" &
pForm!Header_FileUID & "'"
pForm being a paramter passed into the sub using the ME in the forms
private sub event. Even when I change vSQL to be just a valid table
name e.g. "tbl_DataCommentLog" it returns the same error !

I have succesfull managed to use the openrecordset method but this was
when the table to a acces table not a linked one. I think the fact
the vb is not recognising the arguements 'dbOpenDynaset' and
'dbSeeChanges' ( I know this as usually the case of keywords is
corrected, and it wasn't for these words ), is relevant but I don't
understand why !!!!

My tables are linked via a SQL Server driver ODBC connection, is this
significant....?

Cheers Philippa

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<bn0ll6$9d0
$[e-mail]>...

Try

Set vRS = CurrentDb.OpenRecordset(vSQL, dbOpenDynaset, dbseeChanges)

Assuming that vSQL is a valid SQL statement.

Terry

"Philippa" <ph*************@systemc.com> wrote in message
news:9c**************************@posting.google.c om...
> I'm trying to access data in vba using the openrecordset command. The
> data in on a SQL Server 2000 database, and I have linked tables to
> that data. the Table I'm trying to access is one of these linked
> tables, and my codes is as follows:
>
> Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)
>
> when this line is executed I get the error:
>
> "Run-time error '3622':
>
> You must use the dbSeeChanges option with OpenRecordset when
> accessing a SQL Server table that has an Identity column. "
>
> so I changed my code to:
>
> Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)
>
> When this is executed the following error is generated:
>
> " Run-time error '13':
>
> Type mismatch "
>
> I noticed that it didn't seem to recognise the 'dbseechanges'
> arguement as usual the case is correct for known expressions, yet the
> 's' was not captialised on the 'dbseeChanges' !!!!
>
> Please help this is driving me potty...

[Philippa]

Not to worry, while looking in my object browser I noticed that there
was no DAO library, I made a new database and imported all my tables
and stuff, and its all working fine now. Cheers for peoples input.

ph*************@systemc.com (Philippa) wrote in message news:<9c**************************@posting.google. com>...

I'm trying to access data in vba using the openrecordset command. The
data in on a SQL Server 2000 database, and I have linked tables to
that data. the Table I'm trying to access is one of these linked
tables, and my codes is as follows:

Set vRS = CurrentDb.OpenRecordset(tbl_DataCommentLog)

when this line is executed I get the error:

"Run-time error '3622':

You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an Identity column. "

so I changed my code to:

Set vRS = CurrentDb.OpenRecordset(vSQL, , dbseeChanges)

When this is executed the following error is generated:

" Run-time error '13':

Type mismatch "

I noticed that it didn't seem to recognise the 'dbseechanges'
arguement as usual the case is correct for known expressions, yet the
's' was not captialised on the 'dbseeChanges' !!!!

Please help this is driving me potty...