If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Appending data to linked tables
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 |
#2
|
|||
|
|||
Appending data to linked tables
Have you tried to create an append query without using VBA?
-- Build a little, test a little. "BobC" wrote: 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 |
#3
|
|||
|
|||
Appending data to linked tables
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 BobC wrote: 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 -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Appending data to linked tables
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? KARL DEWEY wrote: Have you tried to create an append query without using VBA? |
#5
|
|||
|
|||
Appending data to linked tables
THANK YOU VERY MUCH ... I NOW HAVE A STARTING PLACE! :-) ;- ) :-)
KenSheridan via AccessMonster.com wrote: 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 BobC wrote: 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 |
#6
|
|||
|
|||
Appending data to linked tables
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 KenSheridan via AccessMonster.com wrote: 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 BobC wrote: 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 |
#7
|
|||
|
|||
Appending data to linked tables
On Wed, 14 Oct 2009 21:46:15 -0400, BobC wrote:
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? -- John W. Vinson [MVP] |
#8
|
|||
|
|||
Appending data to linked tables
As John says, its probably the lack of a reference. The one you need to
check is 'Microsoft ActiveX Data Objects #.# Library'. Select the highest version listed. Ken Sheridan Stafford, England BobC wrote: 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: [quoted text clipped - 39 lines] Thanks, Bob -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200910/1 |
Thread Tools | |
Display Modes | |
|
|