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... 5 17860
Try
Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
Assuming that vSQL is a valid SQL statement.
Terry
"Philippa" <ph************ *@systemc.com> wrote in message
news:9c******** *************** ***@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...
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" <te*********@mp s.co.uk> wrote in message news:<bn0ll6$9d 0
$1@newsreaderg1 .core.theplanet .net>... Try
Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
Assuming that vSQL is a valid SQL statement.
Terry
"Philippa" <ph************ *@systemc.com> wrote in message news:9c******** *************** ***@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...
How is vRS declared?
Terry
"Philippa" <ph************ *@systemc.com> wrote in message
news:9c******** *************** **@posting.goog le.com... 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" <te*********@mp s.co.uk> wrote in message news:<bn0ll6$9d 0 $1@newsreaderg1 .core.theplanet .net>... Try
Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
Assuming that vSQL is a valid SQL statement.
Terry
"Philippa" <ph************ *@systemc.com> wrote in message news:9c******** *************** ***@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...
Dim vRS As Recordset
"Terry Kreft" <te*********@mp s.co.uk> wrote in message news:<bn******* ***@newsreaderg 1.core.theplane t.net>... How is vRS declared?
Terry
"Philippa" <ph************ *@systemc.com> wrote in message news:9c******** *************** **@posting.goog le.com... 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" <te*********@mp s.co.uk> wrote in message news:<bn0ll6$9d 0 $1@newsreaderg1 .core.theplanet .net>... Try
Set vRS = CurrentDb.OpenR ecordset(vSQL, dbOpenDynaset, dbseeChanges)
Assuming that vSQL is a valid SQL statement.
Terry
"Philippa" <ph************ *@systemc.com> wrote in message news:9c******** *************** ***@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...
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.go ogle.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... This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Leader |
last post by:
HI,
i got a problem while using StrConv function in sql server.
My requirement is:-
suppose in a name field i have "jhon smith" Now i want to run a sql
which will give me the result like "Jhon Smith"--which means upper
case first.
Now i am running a query like below which is giving me "Jhon smith"
but i want "Jhon Smith".
|
by: ErickR |
last post by:
We are experiencing a problem with Sql Server 2000 linking to an
Access 97 file. We have two machines that link to this .mdb file, and
we recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. The
link on this upgraded machine no longer works, giving this message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider...
|
by: Sudhesh Nayak |
last post by:
Hi,
I have an Oracle (8.1) & a SQL Server 2000 database with
Production data. There are situations when I need data from both the
databases. My first choice was to link Oracle to SQL and run DTS
overnight. But this would have a 1 day latency not to mention the time
it would take.
1. Has any one tried real time access via Linked server to...
|
by: Paul Stanial |
last post by:
How viable is it to use MS Access as a front end (via ODBC) to a SQL Server
2000 database?
The users would access via the internet using a netgear vpn setup.
Thanks,
Paul S
|
by: Brian Salentine |
last post by:
Hi -
We have two SQL 2000 Servers. We have the linked server setup and we
can perform updates and inserts between the databases. But when we add
a trigger and insert something into a table, the database hangs. There
are NO processes blocking or being block in either database. This ONLY
occurs when we have one OS as Windows 2000 Server and...
| |
by: Matthias Jürgens |
last post by:
Ich habe eine Datenbank die vorher komplett in Access war. Jetzt habe
ich die Tabellen von Access auf SQL-Server portiert und in die
Access-Datenbank als Frontend verknüpft.
Jetzt habe ich folgendes Problem:
Vorher als die Tabelle noch in der Access-Datenbank war konnte ich die
Tabelle (oder auch eine Abfrage darauf) öffnen und wenn ich...
|
by: Kamyk |
last post by:
Hello all!
I have such question to all of you.
I have some tables linked from MS SQL Server 2000. Is time of processing
query
based on these linked tables from MS SQL Server 2000, faster or slower than
the time of processing the same query based on tables, which are not linked
but
imported to MS Access?
|
by: Charles Wilt |
last post by:
I have a IBM iSeries (aka AS-400) running v5r3 of OS/400 that I access
via a linked server from SQL Server 2000.
The following select works fine:
select * from prod400db.test.meldbf.InventoryHistory
However, this insert statement fails:
insert into prod400db.TEST.MELDBF.InventoryHistory
|
by: eroshenko |
last post by:
Can somebody point me to the right direction how to create a linked
server to db2 on z/OS using ODBC drivers or OLEDB providers that are
free.
thanks
|
by: dordali |
last post by:
Have a question.
I have a table e.g. "Table1" on DB1 that is going to be archived once a month to "ArchiveTable1" on DB2, both on the one server. Will have to change SPs that were accessing table1 to now access ArchiveTable1. This is no problem as I can just do the following: select * from db2.dbo.ArchiveTable1. Some time in the future this...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| | |