Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unanswered: Appending Records With ADO

    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

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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
    Have a nice day!

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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).
    Have a nice day!

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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.
    Paul

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •