Appending data to linked tables

[BobC]

I needed to create a routine to append 4 linked tables with 4-30
records ... Using 2007.
I have written some VB code and am looking for some basic starting code.
The program already exists and I am modifying it to allow for adding new
records to include appending some records from a existing tables.
One problem may be that some of the field names have spaces in their names.
Any recommendations would be appreciated!
Thanks,
Bob

 

[KARL DEWEY]

Have you tried to create an append query without using VBA?

 

[BobC]

Yes ....
It would normally take 4 append queries that have to be executed in
sequence each time records are to be added (by a 'definite' non-programmer).
I was thinking about macros until I had problems getting a append query
to function. The existing program (written before my time) has field
names with spaces (e.g. Part Number) and the append query on 2007 seems
to refuse to execute ... even when I turn the "Preform Name Autocorrect"
OFF?

 

[BobC]

THANK YOU VERY MUCH ... I NOW HAVE A STARTING PLACE! ;- )

As a hypothetical example here's some code which would append all rows where
the FirstName column is "John" from a table Contacts to a table Johns:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "INSERT INTO Johns([First Name], [Last Name]) "& _
"SELECT FirstName, LastName "& _
"FROM Contacts "& _
"WHERE FirstName = ""John"""

cmd.CommandText = strSQL
cmd.Execute

I've deliberately used column names with spaces in Johns and without in
Contacts. As you see for those with spaces (or other special characters) you
wrap the column name in square brackets in the SQL statement. The same
applies to table names. If in doubt put the brackets around all table and
column names.

Bear in mind that if your 4 tables are related and referential integrity is
enforced, then its important that the 'append' queries like that above are
executed in the correct order. Rows must be inserted into a referenced table
before inserting rows into a referencing table, e.g. it would be necessary
to insert rows into a Customers table before rows which reference those
customers can be inserted into an Orders table.

Ken Sheridan
Stafford, England

I needed to create a routine to append 4 linked tables with 4-30
records ... Using 2007.
I have written some VB code and am looking for some basic starting code.
The program already exists and I am modifying it to allow for adding new
records to include appending some records from a existing tables.
One problem may be that some of the field names have spaces in their names.
Any recommendations would be appreciated!
Thanks,
Bob

 

[BobC]

I am getting a compile error on the first line of code?
I am testing the code using a command button in ACCESS 2007.
My Code is:

Private Sub Command6_Click()
Dim cmd As ADODB.Command
Dim strSQL As String

Dim SVName, SVNumber As String
SVName = "TEST"
SVNumber = "200"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' Define the SQL String
strSQL = "INSERT INTO ServiceVolumeTBL ([SV Number],[SV Name])
VALUES (SVNumber,SVName);"

' Create a new record in the ServiceVolumeTBL
cmd.CommandText = strSQL
cmd.Execute

End Sub

As a hypothetical example here's some code which would append all rows where
the FirstName column is "John" from a table Contacts to a table Johns:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "INSERT INTO Johns([First Name], [Last Name]) "& _
"SELECT FirstName, LastName "& _
"FROM Contacts "& _
"WHERE FirstName = ""John"""

cmd.CommandText = strSQL
cmd.Execute

I've deliberately used column names with spaces in Johns and without in
Contacts. As you see for those with spaces (or other special characters) you
wrap the column name in square brackets in the SQL statement. The same
applies to table names. If in doubt put the brackets around all table and
column names.

Bear in mind that if your 4 tables are related and referential integrity is
enforced, then its important that the 'append' queries like that above are
executed in the correct order. Rows must be inserted into a referenced table
before inserting rows into a referencing table, e.g. it would be necessary
to insert rows into a Customers table before rows which reference those
customers can be inserted into an Orders table.

Ken Sheridan
Stafford, England

I needed to create a routine to append 4 linked tables with 4-30
records ... Using 2007.
I have written some VB code and am looking for some basic starting code.
The program already exists and I am modifying it to allow for adding new
records to include appending some records from a existing tables.
One problem may be that some of the field names have spaces in their names.
Any recommendations would be appreciated!
Thanks,
Bob

 

[John W. Vinson]

I am getting a compile error on the first line of code?
I am testing the code using a command button in ACCESS 2007.
My Code is:

Private Sub Command6_Click()
Dim cmd As ADODB.Command
Dim strSQL As String

Check your References (open the VBA edtior, select Tools... References). Do
you have ADODB checked?