I have a lookup table that contains 12 records -- I want to programmatically append those 12 records to another table from a form (that doesn't have either table as a recordsource). I am unfamiliar with ADO as we have only recently converted to Access 2000. How do I do this? I'm not having much luck finding a clear example of how to write the correct syntax for this.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Appending records using VBA
- Thread starter girlpower
- Start date
There are several ways to do this in ADO.
You can create a recordset object and use the addnew method.
You can create a command object (query) and execute it.
You can create a connection object and use the execute method.
For simplicity, we'll set up a connection object and execute a query.
Sub tat()
Dim objconn As ADODB.Connection
Set objconn = New ADODB.Connection
With objconn
.ConnectionString = "File=<UDLFILENAME>"
.Open
.Execute CommandText:="INSERT INTO ...."
.Close
End With
End Sub
I'm doing this from memory (always a mistake...) so please forgive any stupid mistakes. I've used a link to a udl file, you might want to replace that with your normal connection string. To create a UDL create any file with a UDL extension and double click it. Follow the wizard through to set up a connection. UDL Files are just text files that hold a connection string.
Let us know how it goes.
Chris
You can create a recordset object and use the addnew method.
You can create a command object (query) and execute it.
You can create a connection object and use the execute method.
For simplicity, we'll set up a connection object and execute a query.
Sub tat()
Dim objconn As ADODB.Connection
Set objconn = New ADODB.Connection
With objconn
.ConnectionString = "File=<UDLFILENAME>"
.Open
.Execute CommandText:="INSERT INTO ...."
.Close
End With
End Sub
I'm doing this from memory (always a mistake...) so please forgive any stupid mistakes. I've used a link to a udl file, you might want to replace that with your normal connection string. To create a UDL create any file with a UDL extension and double click it. Follow the wizard through to set up a connection. UDL Files are just text files that hold a connection string.
Let us know how it goes.
Chris
- Status
Similar threads
- Question
