Using Recordsets

[sc5502]

Background:
Front end: Access 2010
Back end: SQL Server 2008



I use the following code to write to a table and it works fine. I change the table name from "tbl_XX02003" to "dbo_XX02033" (line 16). The later is a back end table on SQL Server 2008. After I do this I get the following error: Run time error '3219": Invalid Operation. All I did was change the table name. Do I need something else since "dbo_XX02003" is a back end table?

Dim supplierID As Integer
Dim key As String
Dim typex As String
Dim ratingperiod As String
Dim ratingdate As Date
Dim applevel As String
 
Dim rst As DAO.Recordset
 
supplierID = 11
key = "129FY2Q"
ratingperiod = "FY292Q"
ratingdate = Date
applevel = "Lock"
 
Set rst = CurrentDb.OpenRecordset("tbl_XX02003", dbOpenTable)
rst.AddNew
rst!supplierID = supplierID
rst!key = key
rst!Type = typex
rst!ratingperiod = ratingperiod
rst!ratingdate = ratingdate
rst!applevel = applevel
 
rst.Update
rst.Close
Set rst = Nothing
 
MsgBox "Insert was successful.", 48, ""

[twinnyfo]

sc5502,

I do not know the cause, but I can only assume that it is because of it being a SQL Server table. There may require certain connnections to the DB, which, I must admit, I am not an expert on--however, I am posting on this thread, as I woul dlike to learn and understand more about SQL Server backends, as we may some day move in that direction.

I hope the other experts here have some ideas on solving your problem.

[Seth Schrock]

With the method you are using, you can only open tables that are linked within Access and you must use the name as it appears in Access. I also use the dbOpenDynaset and dbSeeChanges options when opening a recordset.

There are ways to view data in a table that isn't linked, but none of the ways that I know of allow you to add or edit records.

[jforbes]

Hey sc5502,
When opening a Recordset on a SQL table versus an Access table things can get hairy. My guess as to the problem you are running in to has to do with the dbSeeChanges option of the recordset. Maybe try these changes and see if it works?

16. Set rst = CurrentDb.OpenRecordset("dbo_XX02003", dbOpenTable, dbSeeChanges)
 

Or

16. Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_XX02003", dbOpenDynaset, dbSeeChanges)
 

Another thing that might help is switching to using an Execute Statement when you can. You could replace line 16-27 with the following and it should run pretty fast:

Dim sSQL As String
sSQL = ""
sSQL = sSQL & "INSERT INTO dbo_XX02003 ("
sSQL = sSQL & " supplierID"
sSQL = sSQL & ", key"
sSQL = sSQL & ", Type"
sSQL = sSQL & ", ratingperiod"
sSQL = sSQL & ", ratingdate"
sSQL = sSQL & ", applevel"
sSQL = sSQL & ") VALUES ("
sSQL = sSQL & "  " & supplierID & ""
sSQL = sSQL & ", '" & key & "'"
sSQL = sSQL & ", '" & typex & "'"
sSQL = sSQL & ", '" & ratingperiod & "'"
sSQL = sSQL & ", '" & ratingdate & "'"
sSQL = sSQL & ", '" & applevel & "'"
sSQL = sSQL & ")"
DoCmd.SetWarnings False
CurrentDB.Execute sSQL, dbFailOnError + dbSeeChanges
DoCmd.SetWarnings True
 

[zmbd]

That last block of code could be cleaned up in VBA:

'<air code>
'
Dim supplierID As Integer 
Dim key As String 
Dim typex As String 
Dim ratingperiod As String 
Dim ratingdate As Date 
Dim applevel As String
'
Dim sSQL As String
'
'something needs to set the values for key, typex, etc...
'
sSQL = "INSERT INTO dbo_XX02003 (" & _
    "supplierID" & _
    ", key" & _
    ", Type" & _
    ", ratingperiod" & _
    ", ratingdate" & _
    ", applevel)" & _
    " VALUES (" & _
      supplierid & _
    ", '" & Key & "'" & _
    ", '" & typex & "'" & _
    ", '" & ratingperiod & "'" & _
    ", '" & ratingdate & "'" & _
    ", '" & applevel & "')"
'
'CurrentDb.Execute sSQL, dbFailOnError + dbSeeChanges
 

Removed all of the string adding steps and this could be done with fewer lines; however, IMHO, Jforbes has the correct breaking for easy reading.

Also, there is no need to turn off the warnings when useing an execute statement, and indeed the ability to trap the error of the execute method is an advantage.

[sc5502]

This one worked

Set rst = CurrentDb.OpenRecordset("SELECT * FROM dbo_XX02003", dbOpenDynaset, dbSeeChanges)

.