Can a DAO Recordset be updatable when diretly connect to SQL Server (1 Viewer)

aesalazar · Oct 25, 2011

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:

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

mdlueck · Oct 25, 2011

aesalazar said:
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.

aesalazar · Oct 25, 2011

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

mdlueck · Oct 25, 2011

aesalazar said:
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.

mdlueck · Oct 25, 2011

aesalazar said:
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?

aesalazar · Oct 25, 2011

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

mdlueck · Oct 25, 2011

aesalazar said:
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. ????

G37Sam · Oct 25, 2011

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

aesalazar · Oct 25, 2011

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

Galaxiom · Oct 25, 2011

mdlueck said:
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.

Galaxiom · Oct 26, 2011

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"

aesalazar · Oct 26, 2011

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:

Code:

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

jaeutijo2 · May 4, 2012

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

mdlueck · May 7, 2012

aesalazar said:
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.

mdlueck · May 7, 2012

Galaxiom said:
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.

swapna.raj · Jan 14, 2013

Hi,

Even I have the same problem. I am trying to add a record to a table which is in SQl Server 2008 and I receive database is readonly error. I am accessing the table directly which has all fields allow nulls.

I find my recordset not editable. Is there a way out?
Below is my code.
----------------------------
Option Explicit
Dim grsIndexTable As Recordset
Dim grsDocTable As Recordset
Public DBConnection As Database
Public oWorkSpace As Workspace
Public oDbEngine As New PrivDBEngine
Public ErrorLineNum As Integer

Private Sub Form_Load()
On Error GoTo Error_Handler
530 Set oWorkSpace = oDbEngine.CreateWorkspace("Test", "Admin", "")
540 Set DBConnection = oWorkSpace.OpenDatabase("", False, False, "ODBC;DSN=Sales;UID=user;PWD=")
1020 Set grsDocTable = DBConnection.OpenRecordset("DocTab1")
MsgBox (grsDocTable.Updatable)
1234 grsDocTable.AddNew
1120 grsDocTable.Fields("DocID") = 3
1130 grsDocTable.Fields("Path") = "My Path"
1140 grsDocTable.Update
Exit Sub
'---------------
' Error Handler
'---------------
Error_Handler:
If (ErrorLineNum = 0) Then
ErrorLineNum = Erl
End If

MsgBox (ErrorLineNum & Err.Description)
End Sub

----------------------------

jaeutijo · Jan 16, 2013

swapna.raj,

This is how I ended up doing the DAO recordsets. It works just fine.

'set these up on first entering the database
Public gdbTables As DAO.Database

Set gdbTables = DBEngine(0)(0) 'used for SQL Server linked tables

'set this up the first time the user downloads the database to their C (or D) drive
Dim tdf As DAO.TableDef

For Each tdf In gdbTables.TableDefs
'check if table is a linked table
If Len(tdf.Connect) > 0 Then
tdf.Connect = "DRIVER=SQL Server Native Client 10.0;DataTypeCompatibility=80;DATABASE=GCRecovery01;APP=Microsoft Windows Operating System;Trusted_Connection=Yes;SERVER=sqlserver\gcdata;MULTIPLEACTIVERESULTS=Yes;Pooling=True"
tdf.RefreshLink
End If
Next

'and here is how i add a new record to a table
Dim rstARTransactions As DAO.Recordset 'tblARTransactions

Set rstARTransactions = gdbTables.OpenRecordset("SELECT * FROM tblARTransactions WHERE 1 = 2", dbOpenDynaset, dbSeeChanges)
With rstARTransactions
.AddNew 'to tblARTransactions
!ARTransDate = CDate(Me.txtReceivedDate)
!ContractID = mlngContractID
!ARTransTypeID = lngARTransTypeID
!Sequence = rstCheck!Count + 1
!Amount = -1 * Me.txtAmount
!Description = Me.cboPaymentTypeID.Column(3) & " " & Me.txtReference
!PaymentID = CLng(Nz(Me.PaymentID, 0))
!UserID = glngUserID
.Update
End With

'the other way I can add a new record is to do
gdbTables.Execute "INSERT ...", dbSeeChanges

Hope this helps,
Eunice

Rx_ · Jan 22, 2013

Fantastic discussion, so sorry to have missed it!
The last post - "DRIVER=SQL Server Native Client 10.0; with a DSN-Less connection is my favorite.
The data-type conversion from local tables to SQL linked tables has really worked out using this.
My favorite is DAO only because I have been using it since day 1 of DAO.
I still find it faster to create a new Access DB Table(s) in my local databaes for the design phase, them migrate the table(s) over to SQL Server 2008.
Microsoft has a free tool: Microsoft SQL Server Migration Assistant for Access
This tool has created the Identity, indexes, and other necessary triggers. While I have the skills to do all of this directly in SQL myself, during the design phase, Access is very quick to change.
To a degree - this tool is great for a query to become a view in SQL.
Recordsets are updatable.
Looks as if you got some great advice.

Zakraket · May 18, 2016

Old post, but it helped me: DSN-less connecting left me with "un-updatable" DAO-recordsets using DRIVER=SQL Server

DRIVER=SQL Server Native Client 10.0 also didn't work, but DRIVER=SQL Server Native Client 11.0 worked, thanks

Can a DAO Recordset be updatable when diretly connect to SQL Server (1 Viewer)

New member

Sr. Application Developer

New member

Sr. Application Developer

Sr. Application Developer

New member

Sr. Application Developer

Registered User.

New member

Super Moderator

Super Moderator

New member

New member

Sr. Application Developer

Sr. Application Developer

New member

New member

Nothing In Moderation

Registered User.

Similar threads

Users who are viewing this thread