Appending Records With ADO

jtgarrison85 · #1 (**permalink**) 03-01-11, 12:27

I'm moving an Access database with an Access backend to a SQL backend. I have a module that takes an imported XML file then appends the number of records with the next available voucherNumber. The code I have is:

Dim RecordCount As Integer
Dim strSQL As String
Dim db As Database
Dim rec As New ADODB.Recordset

Set db = CurrentDb
strSQL = "Select * from tblVoucherControl Where VoucherNumber is Null"
Set rec = New ADODB.Recordset
rec.Open rec, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rec.MoveFirst
RecordCount = 0
Do Until rec.EOF
rec.Edit
rec!VoucherNumber = DMax("voucherNumber", "tblVoucherControl") + 1
rec.Update
RecordCount = RecordCount + 1
rec.MoveNext
Loop

It was originally written in DAO, but as I understand it, it needs to be changed to ADO, which I'm at a total loss with.

Any help would be MUCH appreciated.

Thanks in advance.

Jeff

Sinndho · #2 (**permalink**) 03-01-11, 13:58

You can still use DAO if you really want to:

Code:

Sub TestSQLInsert()

    Dim strConnectionString As String
    Dim strsql As String
    
    strConnectionString = "ODBC;driver={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;Trusted_Connection=Yes;"
    strsql = "INSERT INTO Tbl_Areas ( FK_Tbl_Users, Area_Name, Comment, Inactive ) " & _
              "VALUES ( 1, 'First Area', 'This is a comment', 0 );"
    SQLExecute strConnectionString, strsql

End Sub

Sub SQLExecute(ByVal Connection As String, ByVal SQL As String)

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strQryName As String
    
    strQryName = "Qry_Insert_" & Format(Now, "yy-mm-dd-hh-nn-ss")
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef
    With qdf
        .Connect = Connection
        .Name = strQryName
        .SQL = SQL
        .ReturnsRecords = False
    End With
    dbs.QueryDefs.Append qdf
    dbs.Execute strQryName
    dbs.QueryDefs.Delete strQryName
    Set qdf = Nothing
    Set dbs = Nothing

End Sub

pbaldy · #3 (**permalink**) 03-01-11, 14:23

For the record, as long as the SQL Server tables are linked, your DAO recordset method will probably still work fine. ADO is considered to be better optimized to work with SQL Server, but DAO works fine.

Sinndho · #4 (**permalink**) 03-01-11, 16:16

Paul,

The example I provided does not need the table to be attached and there is no permanent link to the server.

The only thing that changed from 2003 to 2007/2010 is that you cannot open an ODBC workspace any more (Set wksSQL = CreateWorkspace ,,, dbUseODBC now produces an error), so all cursors are on the client side (wksSQL.DefaultCursorDriver = dbUseODBCCursor produces an error too).

pbaldy · #5 (**permalink**) 03-01-11, 16:22

I was not trying to imply that your method wouldn't work, just correcting the OP's misconception that DAO doesn't work with SQL Server. It works fine with linked SQL Server tables, and my guess would be that most people work with linked tables.

Sinndho · #6 (**permalink**) 03-01-11, 16:44

Paul,

No problem

I did not answered because I thought you implied that. However, I felt necessary to add some precisions. Since Access 2007/2010 was issued I read many confusing things (not here!) saying that without linked tables you could not work with DAO at all with the new versions of Access. I just wanted to clarify the topic.

Internet Services

Web Development

Digital Marketing

Consumer Tech