Hi Guys. I have what I think is a simple enough question but an hour of searching hasnt come up with a direct answer. I think it is "No" but wanted to make sure.
We have an old program in VB6 written years ago that I have been keeping a float for the past several years. It has always be a VB6 front end that hits a Access (JET) database via DAO.
I would like to get the project running on a SQL Server database as the backend and avoid using Access as the middle man via Linked Tables. In other words, I was hoping to update the VB code to be able to connect directly to SQL Server but NOT have to rewrite all of the DAO code that rely on editing the database tables via Recordsets.
But, I keep running into the error that the Recordsets are not updatable when I try to do a rst.edit. Knowing that DAO was mostly designed to work with JET, I assume that it CANNOT edit via a direct connection to SQL Server?
Here is some code:
We have an old program in VB6 written years ago that I have been keeping a float for the past several years. It has always be a VB6 front end that hits a Access (JET) database via DAO.
I would like to get the project running on a SQL Server database as the backend and avoid using Access as the middle man via Linked Tables. In other words, I was hoping to update the VB code to be able to connect directly to SQL Server but NOT have to rewrite all of the DAO code that rely on editing the database tables via Recordsets.
But, I keep running into the error that the Recordsets are not updatable when I try to do a rst.edit. Knowing that DAO was mostly designed to work with JET, I assume that it CANNOT edit via a direct connection to SQL Server?
Here is some code:
Code:
dbsSQL As DAO.Database
Set dbsSQL = OpenDatabase("", dbDriverNoPrompt, False, ODBC;Driver={SQL Server};" & _
Server={FS\SQLSERVER_1};UID=SQL_Editor;PWD=Edit_SQL;)
Dim rstTEMP As DAO.Recordset
Set rstTEMP = dbsSQL .OpenRecordset(strSQLQ, dbOpenDynaset, dbSeeChanges)
rstTEMP.Edit '= BIG FAT ERROR
rstTEMP("CUR_ID") = int_tdms_loc_id
rstTEMP.Update