How to append Records to a table via VBA?

[jtgarrison]

I'm moving a database from an Access backend to a SQL Server 2005 backend.

What I need to do is to append the number of records that get pulled in from a table and increment the voucherNumber field to the next available one.

The code below was originally written in DAO.

  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

Any help would be MUCH appreciated.

Thanks.
Jeff

[Rabbit]

DoCmd.RunSQL "INSERT INTO TableName (FieldList) VALUES (ValueList)"

[jtgarrison]

It would need to get a count of the the records it imported, loop through them and give it a number (voucherNumber) .

[Rabbit]

You want to append, i.e. add a new record, to a table. INSERT INTO is the command to append a record.

[jtgarrison]

Ok....what about looping through the records that are going to be added? It will not be the same number of records each time the process is run.

[Rabbit]

Since you're using SQL Server as your backend, you can use the ROW_NUMBER function. That way you can add a sequential number.

[MrDeej]

I am not sure what you want, but some things i can help you with.

First off all you have written code in both ADO and DAO. I think ADO is faster and more programming friendly. Secondly you have added excess code wich dosent do anything.

Here is ADO to insert posts.

dim Rst as new adodb.recordset 
with rst
.open "Select * from [yourtable],currentproject.connection,adopenforwardonly,adlockoptimistic
.addnew
!field = value 
.update
.close 
end with

Your Dmax function should get the right number