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:


dbsSQL As DAO.Database
Set dbsSQL = OpenDatabase("", dbDriverNoPrompt, False, ODBC;Driver={SQL Server};" & _
Server={FS\SQLSERVER_1};UID=SQL_Editor;PWD=Edit_SQ L;)

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

But, I keep running into the error that the Recordsets are not updatable when I try to do a rst.edit.

Sounds like there is no key by which to uniquely identify the record being edited from all of the rest. You really need some sort of key, best would be a SQL Server IDENTITY column which will auto-generate unique ID's. When doing the update, the unique ID will be used to identity to SQL Server which record needs to be updated. IE you never directly edit that field... leave it blank when inserting new records, and update by that field when issuing SQL UPDATE commands. Or if edited via the UI, then that error should also be eliminated.

Thanks for the rely mdlueck.

Actually, every table has a KEY field in SQL Server. It is actually a SP that runs when the database is imported from access to add column called "SQLID" and set it as an Identity with an auto increment of 1.

So if I read the second half of what you wrote corrrectly, you are saying that the updates have to be done via SQL commands (update, insert, etc.), correct? That was my basic question I suppose - Can I update SS data using a Recordset object and not have to rewrite all of the VB code to use SQL commands. Sounds like the answer is No?

Thanks
Ernie

So if I read the second half of what you wrote corrrectly,...

I later thought that you might have forms bound to the BE DB. Thus added the bit about directly updating the BE via form fields later in my reply to you.

Odd that you would get that error if you indeed had a key set on the table. The only way I have encountered that error message is with tables without some way to uniquely identify a record.

Can I update SS data using a Recordset object and not have to rewrite all of the VB code to use SQL commands.

BTW: What is SS data?

LOL, sorry. SS = SQL Server.

All of my searching seems to indicated that rst.Edit / Update will not work on an ODBC connection. The only way to get it to work would be to used Linked tables in Access and connect via JET from VB. But the performance is horrible. Even with the ODBC connection via DAO it is noticeably slower but at least acceptable.

The right way to do it would be to switch to ADO but that would be a major task. Rather just rewrite the program in .NET if that is the case.

Thanks again for all your help.

Ernie

All of my searching seems to indicated that rst.Edit / Update will not work on an ODBC connection. The only way to get it to work would be to used Linked tables in Access and connect via JET from VB.

How would you think to edit records in SQL Server without using a Linked table or ODBC? I know of no other way. Even ADO in Access connects to SQL Server via ODBC, even DSN-less is still ODBC. ????

You're missing a " before ODBC, second line of your code btw

I tried using DAO to connect to SQL server DBs but couldn't for some reason, ran across a ton of errors every time. Upon research I read that ADO was a much better approach for non Access DB's. I couldn't be happier with ADO

Sam, your right. It wasnt a direct copy paste, I patched that code together because the full code would have been way to long. That actual version has the double quotes at both ends.

mdlueck, as far as I can tell DAO Recordsets cannot edit ODBC (at least when it comes to SQL Server). You either go use db.Execute with SQL commands or switch to ADO. Again, if I would be happy to be proven wrong here! Otherwise its a whole lot of rewriting and debugging :/

Ernie

How would you think to edit records in SQL Server without using a Linked table or ODBC? I know of no other way. Even ADO in Access connects to SQL Server via ODBC, even DSN-less is still ODBC. ????

ADO can also connect using an OLEDB connection string.

DAO Dynaset Recordsets connected by ODBC to MSSQL Server are definitely Updateable.

Are you sure you are not at EOF? Otherwise maybe you have a permissions problem in the database.

An error code or description would be a lot more useful than "BIG FAT ERROR"

The error is that it is not updatable - Err 3027 - "Cannot Update. Database or object is read-only". When I check rstTEMP.Updatable, it is set to False. But g_dbsTDMS.Updatable is set to True.

I am positive it is not EOF. I put the full code below. It is able to retrive data from the same record it attempts to edit. When connected to Access it works fine. When it is SQL S, it chokes on that line.

Here is the code for the entire procedure:


Function GetNextLocId$()
Dim rstTEMP As Recordset
Dim strSQLQ As String
Dim int_tdms_loc_id As Integer
Dim int_remain As Integer

GetNextLocId$ = ""

strSQLQ = "SELECT * FROM FRANKLIN_LOC_ID_MANAGER WHERE STATUS = 'X'"
Set rstTEMP = g_dbsTDMS.OpenRecordset(strSQLQ)
If rstTEMP.EOF Then
MsgBox "There is no active block of for new location IDs. Please Notify the System Administrator", 48, "Socrates Add Location"
Else
If Val("" & rstTEMP("CUR_ID")) = 0 Then
int_tdms_loc_id = Val("" & rstTEMP("START_ID"))
Else
int_tdms_loc_id = Val("" & rstTEMP("CUR_ID")) + 1
End If
If int_tdms_loc_id > rstTEMP("LIMIT_ID") Then
int_remain = Val("" & rstTEMP("END_ID")) - int_tdms_loc_id
MsgBox ("There Are " & int_remain & " Spaces Remaining For New Location IDs. Please Notify the System Administrator"), 48, "Socrates Add Location"
End If
If int_tdms_loc_id >= Val("" & rstTEMP("END_ID")) Then
int_remain = rstTEMP("END_ID") - int_tdms_loc_id
MsgBox ("You Cannot Add Any More Locations. Please Notify the System Administrator"), 48, "Socrates Add Location"
Exit Function
End If

rstTEMP.Edit '= Error 3027
rstTEMP("CUR_ID") = int_tdms_loc_id
rstTEMP.Update
GetNextLocId$ = Format$(int_tdms_loc_id, "0000000000")
End If
Set rstTEMP = Nothing

DoEvents

End Function

I find I have to use dbOpenDynaset and dbSeeChanges and can never just open a entire table, always have to use a SELECT to open a DAO.recordset. Here's an example.

Public gdbTables As DAO.Database
Public gdb As DAO.Database
Set gdbTables = DBEngine(0)(0) 'or possibly current db
Set gdb = DBEngine(0)(0)
(also using linked tables, with ODBC of SQL Server Native Client 10)

strSQL = "UPDATE tblMonitoringHistory INNER JOIN tblSCAPTransactions ON tblMonitoringHistory.MonitorHistoryID = tblSCAPTransactions.MonitorHistoryID " & _
"SET tblSCAPTransactions.SCAPTransDate = #" & Me.txtMonitorDateTo & "#, tblSCAPTransactions.Sequence = " & lngSeq & ", tblSCAPTransactions.ModDate = date() " & _
"WHERE tblMonitoringHistory.ContractVehicleID = " & Me.txtCVID & " AND tblMonitoringHistory.MonitorDate = #" & Me.txtMonitorDateTo & "# AND SCAPTransTypeID = " & GetTableCodeID("tblSCAPTransTypes", SC_AP_MONITORING, "SCAPTransTypeID")
gdb.Execute strSQL, dbSeeChanges
strSQL = "SELECT Sequence FROM tblSCAPTransactions " & _
"WHERE SCAPTransDate = #" & Me.txtMonitorDate & "# AND SCID = " & Me.txtSCID & " AND Sequence > " & lngOriginalSeq & " AND SCAPTransTypeID = " & GetTableCodeID("tblSCAPTransTypes", SC_AP_MONITORING, "SCAPTransTypeID") & " ORDER BY Sequence"
Set rst = gdbTables.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges) 'Sequence
Do While Not rst.EOF
rst.Edit
rst!Sequence = rst!Sequence - 1
rst.Update
rst.MoveNext
Loop

It is actually a SP that runs when the database is imported from access to add column called "SQLID" and set it as an Identity with an auto increment of 1.

So if I read the second half of what you wrote corrrectly, you are saying that the updates have to be done via SQL commands (update, insert, etc.), correct?

Well if that which is providing the records from SQL Server to Access is indeed a Stored Procedure (SP), then of course SP's which SELECT records have no ability to INSERT / UPDATE. You must use additional SP's to INSERT / UPDATE "checked out" records.

1) SP to SELECT records
2) SP to INSERT new record
3) SP to UPDATE existing record

I would download records into a read-only FE temp table. INSERT / UPDATE should be performed from an ADD / EDIT record form which the record list form brings up to edit ONE record at a time. The SP sends the record back to the BE DB, and from there (knowing the key of that record) download that one record from the BE DB to the FE temp table.

DAO Dynaset Recordsets connected by ODBC to MSSQL Server are definitely Updateable.

Not if that which the connection goes to in the BE DB is a Stored Procedure which SELECTs records.

If one connects to the table itself, THEN it could be updatable.