Access 2000 Linked to SQL Server 2000, openRecordset error

**Terry Kreft** · Nov 12 '05, 03:31 PM

Re: Access 2000 Linked to SQL Server 2000, openRecordset error

Try

Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)

Assuming that vSQL is a valid SQL statement.

Terry

"Philippa" <philippa.speir s@systemc.com> wrote in message
news:9cf39c04.0 310200350.7e629 291@posting.goo gle.com...[color=blue]
> 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.OpenR ecordset(tbl_Da taCommentLog)
>
> 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.OpenR ecordset(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...[/color]

**Philippa** · Nov 12 '05, 03:33 PM

Re: Access 2000 Linked to SQL Server 2000, openRecordset error

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_DataComment Log WHERE Comment_FileUID = '" &
pForm!Header_Fi leUID & "'"
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_DataCommen tLog" 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" <terry.kreft@mp s.co.uk> wrote in message news:<bn0ll6$9d 0
$1@newsreaderg1 .core.theplanet .net>...[color=blue]
> Try
>
> Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
>
> Assuming that vSQL is a valid SQL statement.
>
> Terry
>
> "Philippa" <philippa.speir s@systemc.com> wrote in message
> news:9cf39c04.0 310200350.7e629 291@posting.goo gle.com...[color=green]
> > 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.OpenR ecordset(tbl_Da taCommentLog)
> >
> > 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.OpenR ecordset(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...[/color][/color]

**Terry Kreft** · Nov 12 '05, 03:33 PM

Re: Access 2000 Linked to SQL Server 2000, openRecordset error

How is vRS declared?

Terry

"Philippa" <philippa.speir s@systemc.com> wrote in message
news:9cf39c04.0 310210308.ae736 6d@posting.goog le.com...[color=blue]
> 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_DataComment Log WHERE Comment_FileUID = '" &
> pForm!Header_Fi leUID & "'"
> 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_DataCommen tLog" 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" <terry.kreft@mp s.co.uk> wrote in message news:<bn0ll6$9d 0
> $1@newsreaderg1 .core.theplanet .net>...[color=green]
> > Try
> >
> > Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
> >
> > Assuming that vSQL is a valid SQL statement.
> >
> > Terry
> >
> > "Philippa" <philippa.speir s@systemc.com> wrote in message
> > news:9cf39c04.0 310200350.7e629 291@posting.goo gle.com...[color=darkred]
> > > 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.OpenR ecordset(tbl_Da taCommentLog)
> > >
> > > 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.OpenR ecordset(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...[/color][/color][/color]

**Philippa** · Nov 12 '05, 03:35 PM

Re: Access 2000 Linked to SQL Server 2000, openRecordset error

Dim vRS As Recordset

"Terry Kreft" <terry.kreft@mp s.co.uk> wrote in message news:<bn3k77$br 6$1@newsreaderg 1.core.theplane t.net>...[color=blue]
> How is vRS declared?
>
>
> Terry
>
> "Philippa" <philippa.speir s@systemc.com> wrote in message
> news:9cf39c04.0 310210308.ae736 6d@posting.goog le.com...[color=green]
> > 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_DataComment Log WHERE Comment_FileUID = '" &
> > pForm!Header_Fi leUID & "'"
> > 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_DataCommen tLog" 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" <terry.kreft@mp s.co.uk> wrote in message news:<bn0ll6$9d 0
> > $1@newsreaderg1 .core.theplanet .net>...[color=darkred]
> > > Try
> > >
> > > Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
> > >
> > > Assuming that vSQL is a valid SQL statement.
> > >
> > > Terry
> > >
> > > "Philippa" <philippa.speir s@systemc.com> wrote in message
> > > news:9cf39c04.0 310200350.7e629 291@posting.goo gle.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.OpenR ecordset(tbl_Da taCommentLog)
> > > >
> > > > 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.OpenR ecordset(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...[/color][/color][/color]

**Philippa** · Nov 12 '05, 03:38 PM

Re: Access 2000 Linked to SQL Server 2000, openRecordset error

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.

philippa.speirs @systemc.com (Philippa) wrote in message news:<9cf39c04. 0310200350.7e62 9291@posting.go ogle.com>...[color=blue]
> 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.OpenR ecordset(tbl_Da taCommentLog)
>
> 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.OpenR ecordset(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...[/color]