Recordset question in access?

J

Josh Grameson

Here is a piece of code that works when I run it against a table that is
linked from another MDB, but when I run it against a table on SQL Server
(linked with ODBC) it does not. And I'm wondering why, and is there away to
get it to work?


****SQL Server Table:
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

Here is get an error. Same if I don't do rs.Update.

End Sub


****Linked Access Table
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

this works fine here
End Sub
 
S

Sylvain Lafontaine

It doesn't work because you are mixing different concepts in the same bag.

First, ODBC are old drivers and won't give you the same performance/results
as newer drivers like OLEDB.

Second; linked tables are local alias representing specific connections to
existing (real) tables on a SQL-Server but are not tables by themselves.
DAO can use such a alias to open a SQL-Server table because this alias give
it the connection string to the SQL-Server and the name of the table to
connect to. However; for ADODB and SQL-Server, an Access local alias such
as [dbo_tblCustomer] means absolutely nothing.

In your case, you should write something like this when connecting to a
SQL-Server via linked tables (tested with an OLEDB connection, not ODBC) :

Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("dbo_tblCustomer", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)

rs.AddNew
rs("name") = "New Name"
rs.Update
rs.Bookmark = rs.LastModified

MsgBox rs("name")

S. L.
 
S

Sylvain Lafontaine

I forgot to mention that this newsgroup is about ADP, not MDB with linked
tables.

You should ask your questions in the m.p.access.externaldata newsgroup.

S. L.

Sylvain Lafontaine said:
It doesn't work because you are mixing different concepts in the same bag.

First, ODBC are old drivers and won't give you the same
performance/results as newer drivers like OLEDB.

Second; linked tables are local alias representing specific connections to
existing (real) tables on a SQL-Server but are not tables by themselves.
DAO can use such a alias to open a SQL-Server table because this alias
give it the connection string to the SQL-Server and the name of the table
to connect to. However; for ADODB and SQL-Server, an Access local alias
such as [dbo_tblCustomer] means absolutely nothing.

In your case, you should write something like this when connecting to a
SQL-Server via linked tables (tested with an OLEDB connection, not ODBC) :

Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("dbo_tblCustomer", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)

rs.AddNew
rs("name") = "New Name"
rs.Update
rs.Bookmark = rs.LastModified

MsgBox rs("name")

S. L.

Josh Grameson said:
Here is a piece of code that works when I run it against a table that is
linked from another MDB, but when I run it against a table on SQL Server
(linked with ODBC) it does not. And I'm wondering why, and is there away
to get it to work?


****SQL Server Table:
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

Here is get an error. Same if I don't do rs.Update.

End Sub


****Linked Access Table
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

this works fine here
End Sub
Click to expand...
Click to expand...
 
J

Josh Grameson

HI Sylvain,


I get the same error when using your code, as I get when using the code that
I posted here.


Error from the code that I posted:
run-time error '-2147217887 (80040e21)':

ODBC--call failed


Error from the code that you posted:
Run-time error '3146'

ODBC--call failed




Sylvain Lafontaine said:
It doesn't work because you are mixing different concepts in the same bag.

First, ODBC are old drivers and won't give you the same
performance/results as newer drivers like OLEDB.

Second; linked tables are local alias representing specific connections to
existing (real) tables on a SQL-Server but are not tables by themselves.
DAO can use such a alias to open a SQL-Server table because this alias
give it the connection string to the SQL-Server and the name of the table
to connect to. However; for ADODB and SQL-Server, an Access local alias
such as [dbo_tblCustomer] means absolutely nothing.

In your case, you should write something like this when connecting to a
SQL-Server via linked tables (tested with an OLEDB connection, not ODBC) :

Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("dbo_tblCustomer", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)

rs.AddNew
rs("name") = "New Name"
rs.Update
rs.Bookmark = rs.LastModified

MsgBox rs("name")

S. L.

Josh Grameson said:
Here is a piece of code that works when I run it against a table that is
linked from another MDB, but when I run it against a table on SQL Server
(linked with ODBC) it does not. And I'm wondering why, and is there away
to get it to work?


****SQL Server Table:
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

Here is get an error. Same if I don't do rs.Update.

End Sub


****Linked Access Table
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

this works fine here
End Sub
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

I'm not sure why. Maybe because you are using an ODBC connection instead of
an OLEDB. There are other possibilities, too, but I cannot tell you more
because I'm not in front of your database.

This newsgroup is for ADP; you should ask your question, with the maximum of
details, in the m.p.access.externaldata newsgroup.

S. L.

Josh Grameson said:
HI Sylvain,


I get the same error when using your code, as I get when using the code
that I posted here.


Error from the code that I posted:
run-time error '-2147217887 (80040e21)':

ODBC--call failed


Error from the code that you posted:
Run-time error '3146'

ODBC--call failed




Sylvain Lafontaine said:
It doesn't work because you are mixing different concepts in the same
bag.

First, ODBC are old drivers and won't give you the same
performance/results as newer drivers like OLEDB.

Second; linked tables are local alias representing specific connections
to existing (real) tables on a SQL-Server but are not tables by
themselves. DAO can use such a alias to open a SQL-Server table because
this alias give it the connection string to the SQL-Server and the name
of the table to connect to. However; for ADODB and SQL-Server, an Access
local alias such as [dbo_tblCustomer] means absolutely nothing.

In your case, you should write something like this when connecting to a
SQL-Server via linked tables (tested with an OLEDB connection, not ODBC)
:

Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("dbo_tblCustomer", dbOpenDynaset,
dbAppendOnly Or dbSeeChanges)

rs.AddNew
rs("name") = "New Name"
rs.Update
rs.Bookmark = rs.LastModified

MsgBox rs("name")

S. L.

Josh Grameson said:
Here is a piece of code that works when I run it against a table that is
linked from another MDB, but when I run it against a table on SQL Server
(linked with ODBC) it does not. And I'm wondering why, and is there away
to get it to work?


****SQL Server Table:
Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs.AddNew
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

Here is get an error. Same if I don't do rs.Update.

End Sub


****Linked Access Table
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "[tblCustomer]", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rs("name") = "New Name"
rs.Update
MsgBox rs("name")

this works fine here
End Sub
Click to expand...
Click to expand...
Click to expand...
 
V

Vadim Rapp

Hello Josh:
You wrote in conference microsoft.public.access.adp.sqlserver on Sat, 27
Nov 2004 17:18:50 GMT:

JG> Here is a piece of code that works when I run it against a table that
JG> is linked from another MDB, but when I run it against a table on SQL
JG> Server (linked with ODBC) it does not. And I'm wondering why, and is
JG> there away to get it to work?

JG> ****SQL Server Table:
JG> Private Sub Command0_Click()
JG> Dim rs As ADODB.Recordset
JG> Set rs = New ADODB.Recordset
JG> rs.Open "[dbo_tblCustomer]", CurrentProject.Connection, adOpenKeyset,
JG> adLockOptimistic
JG> rs.AddNew
JG> rs("name") = "New Name"
JG> rs.Update
JG> MsgBox rs("name")

JG> Here is get an error. Same if I don't do rs.Update.

what error?


Vadim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question in Access Recordset 3
Adding record on sql server? 2
migrating code from Access 2000 to 2003 and 2007 5
Code Conversion for Use in .adp 1
ADO Access For Value List Error 4
Import Txt files into an ADP 2
ADODB.Recordset 6
Read from image file into IMAGE column 1

Top