Writing Records to Table in VBA

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

What I'd like to do is:

Open a table, look at each record in the table, write the
record two or more times to a new table making a slight
adjustment to each output record.

All in VBA that's contained the database module.

Any sample code out there?
 
Assuming you've got a reference set to DAO, it would be something like:

Dim dbCurr As Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT * FROM MyTable")
With rsCurr
Do While Not .EOF
strSQL = "INSERT INTO MyTable2 " & _
"(Field1, Field2, Field3) " & _
"VALUES (" & _
!Field1 & ", " & _
!Field2 & ", " & _
!Field3 & ")"
dbCurr.Execute strSQL, dbFailOnError
.MoveNext
Loop
End With
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing

Where I'm setting strSQL, you can make whatever changes you want to the
values of the fields in the original table.

Note that you'll need to use Chr$(34) & !Field1 & Chr$(34) if Field1 is a
text field, and Format$(!Field1, "\#mm\/dd\/yyyy\#") if it's a Date field.

On the other hand if all you want to do is make some simple changes to the
existing values, an Append query may be more appropriate.

INSERT INTO MyTable2 (Field1, Field2, Field3)
SELECT Field1 * 2, "Name: " & FIeld2, DateAdd("m", 1, Field3)
FROM MyTable
 
Thanks for the idea.

Looks like the ticket.

How can I set the reference? I assume I would do this in
the VBA interface.

Also "Set dbCurr = CurrentDb()" tells vba to look in the
Access Database that the module reides?

Have a good holiday.

Mark
 
With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it.

And yes, CurrentDb points to the database in which the module resides.
 
WHen I tried to use this I received an error on this Dim mydb As Database-User defined type not defined.
 
That's because you must not have a reference set for DAO. As I said
elsewhere in this thread, "With any code module open, select Tools |
References from the menu bar, scroll through the list of available
references until you find the one for Microsoft DAO 3.6 Object Library, and
select it."


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Bob said:
WHen I tried to use this I received an error on this Dim mydb As
Click to expand...
Database-User defined type not defined.
 
Back
Top