469,330 Members | 1,295 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

How to append Records to a table via VBA?

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.

Expand|Select|Wrap|Line Numbers
  1.   Dim RecordCount As Integer
  2.     Dim strSQL As String
  3.     Dim db As Database
  4.     Dim rec As New ADODB.Recordset
  6.     Set db = CurrentDb
  7.     strSQL = "Select * from tblVoucherControl Where VoucherNumber is Null"
  8.     Set rec = New ADODB.Recordset
  9.     rec.Open rec, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
  11.     rec.MoveFirst
  12.     RecordCount = 0
  13.     Do Until rec.EOF
  14.         rec.Edit
  15.         rec!VoucherNumber = DMax("voucherNumber", "tblVoucherControl") + 1
  16.         rec.Update
  17.         RecordCount = RecordCount + 1
  18.       rec.MoveNext
  19.     Loop
Any help would be MUCH appreciated.

Mar 1 '11 #1
6 20270
12,516 Expert Mod 8TB
DoCmd.RunSQL "INSERT INTO TableName (FieldList) VALUES (ValueList)"
Mar 1 '11 #2
It would need to get a count of the the records it imported, loop through them and give it a number (voucherNumber).
Mar 1 '11 #3
12,516 Expert Mod 8TB
You want to append, i.e. add a new record, to a table. INSERT INTO is the command to append a record.
Mar 1 '11 #4
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.
Mar 1 '11 #5
12,516 Expert Mod 8TB
Since you're using SQL Server as your backend, you can use the ROW_NUMBER function. That way you can add a sequential number.
Mar 1 '11 #6
157 100+
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.

Expand|Select|Wrap|Line Numbers
  1. dim Rst as new adodb.recordset 
  2. with rst
  3. .open "Select * from [yourtable],currentproject.connection,adopenforwardonly,adlockoptimistic
  4. .addnew
  5. !field = value 
  6. .update
  7. .close 
  8. end with
Your Dmax function should get the right number
Mar 2 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.