Updating A SQL server table through DAO and ODBC In Access 2000
You will have to use an ADO recordset inside your Acc97 database. You will set a reference to the ADO object. The details on making the connection read/write can be found in the ADO help files since I am not that proficient with ADO yet..(you have to make it a server side cursor)
Here is the rub though, since the references are scanned by the VBA engine in alphabetical order AND both ADO and DAO have a database and recordset object, once you reference ADO inside your project, VBA will think all your RS are of type ADO -- to fix this always explicity declare your objects:
DIM db AS DAO.Database instead of DIM db as Database
DIM rs AS DAO.Recordset instead of DIM rs as Recordset
for the ADO objects you use:
DIM rs as ADO.recordset
enjoy,
Patrick McCarthy
PS - you could use CREATEOBJECT() instead of referencing the ADO library in Access -- it saves time searching for all your objects and explicitly delcaring the library. A note: Start to explicitly declare ALL your objects to avoid the problem in the future....
Thanks, but I'm already doing the equivalent in my code. strConnect, declared as a global variable, is the connection string that I use for the SQL Server database throughout my code (It works just fine to connect). As I said, " I can run stored procedures and pass through queries but can't pull out an updateable query like I can with an attached ODBC or MDB table." How do you make an updateable query through an ODBC connection with DAO, by not attaching the table?
You need to create a CONNECTION object from an ODBC workspace - then you can
open recordsets and/or execute SQL against it. Check out the
createworkspace method in the help file...
PKM
I have a Database of clients in a SQL Server 7 database that I'm cleaning up
the cities and addresses (left over from a cruddy Mac database). What is the
problem with my code to so I can have an updateable recordset? I know that
pass through queries are not updateable. I can run stored procedures and
pass through queries but can't pull out an updatable query like I can with
an attached ODBC or MDB table.
Set db = CurrentDb
strSQL = "Select ClientRecID, Address, City, State From Clients"
Set qryClient = db.CreateQueryDef("")
qryClient.Connect = strConnect
qryClient.SQL = strSQL
Set rstClient = qryClient.OpenRecordset
rstClient.Edit (this is the point that it fails)
Any help would be greatly appreciated.
Thanks,
Steve House
