Recordset question in access?

  • Thread starter Thread starter Josh Grameson
  • Start date Start date
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
 
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.
 
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...
 
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...
 
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...
 
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
 
Back
Top