SQL Parameter

[ASharifs]

Hi

I am trying to Append 100's of diffrent tables to one master table that have the the same fields. The tables are at diffrent locations, and once it's done I want to start with a new master table name and append to it many child tables of its own family and so on. I used the following SQL command in a Mcro:

INSERT INTO TABLE1 SELECT * FROM TABLE2

but as you can see the child tables and their location is fixed. I tried to use the command '&' before the table name that prompts the user for the table name but unfortunately that doesn't work with Access. I also created one pop-up form, changed the properties as recommended in an Access book .. then what ?

Is it possible to let the user specify the table name and it's path ?

Appreciation your help ..

 

[Jon K]

Use the IN Clause to specify the destination database, e.g.

INSERT INTO TABLE1 IN "D:\FolderName\Destination.mdb"
SELECT *
FROM TABLE2;


Note: The data to be inserted must not violate the primary key and unique indexes of Table1

 

[ASharifs]

Thanks for the reply.

Actually I wanted the end user to be prompted to input the mother table name ( Destination ); and prompted to type the name/location of the child table ( Source ).

Thanks ..

 

[Jon K]

You will need to use some VBA code.


Put two text boxes on a form. Name them as txtDestination and txtSource.

Add a command button on the form and put this code in its On Click event:-
----------------------------
Private Sub Command0_Click()

On Error GoTo Err_Handler

Dim SQL As String

SQL = "INSERT INTO TABLE1 IN '" & Me.txtDestination & "' SELECT * FROM " & Me.txtSource
DoCmd.RunSQL SQL
Exit Sub

Err_Handler:
MsgBox Err.Description

End Sub
---------------------------

When the user enters the destination e.g. D:\FolderName\Destination.mdb and the source table e.g. Table2, and clicks on the command button, Table2 will be inserted into Table1 of the destination database.

 

[ASharifs]

Thanks .. it worked !.

There is one little problem: It works only if the Destination table was the only one in the database file; or if it was the first among the other tables ! so I had to rename it till it was on top of the tables list. Is there anyway to solve this ?.

May I also ask the function of the single quote ' ..

Also, the source may come from diffrent places. Would it be better to specify the path or shall I use another macro ( transferedatabase ) in order to transfrer it first to the same location ; run the appened command button; and then delete the table once it's appeneded (source table ) as all the 100's of source tables will have the same name.

Thanks again for your effort ..

Ahmad

 

[Jon K]

On my system, the destination table can be positioned anywhere amongst the tables. I have no idea why it has to the first table on your system.

The location of the mdb has to be in one text string. A text string can be enclosed by double quotes or single quotes. Since it is placed inside another string, the single quotes are used.


If I understand your question correctly, the source tables (all of the same name) in various mdbs are to be inserted into one destination table in the destination mdb (whose location is fixed.) It would be simpler to work from a form in the destination mdb, instead of the source mdbs.

Add a form in the destination mdb. Put a text box on the form and name it as txtSourceMDB.

Add a button on the form and put the follow code in its On Click event (replacing with the correct destination and source table names):-
----------------------------
Private Sub Command0_Click()

On Error GoTo Err_Handler

Dim SQL As String

SQL = "Insert into [destination table name]" & _
" select * from [source table name] in '" & Me.txtSourceMDB & "'"

DoCmd.RunSQL SQL
Exit Sub

Err_Handler:
MsgBox Err.Description

End Sub
----------------------------

As the destination table is now hard-coded in the SQL statement, hopefully it will work smoothly when a source mdb (e.g. D:\SourceFolder\Source.mdb) is entered in the text box and the command button is clicked.

 

[ASharifs]

Lovely !. It worked and I really appreciate your help.

Only if you have time and this is just to make it even better .. The tables names ( both destination and the source ) are hard-coded. Beside specifying the source root Is it also possible to let the end user specify the table name as well ?.

Am not sure of the function of the sqr brackets []; also is '&' used to concatonate ?!

Thanks,

 

[Jon K]

It is possible for the user to specify the source table, too. Just add a text box txtSourceTable on the form, and change the SQL in the code to:-

SQL = "Insert into [destination table name]" & _
" select * from [" & Me.txtSourceTable & "] in '" & _
Me.txtSourceMDB & "'"


If a table name or a field name contains spaces or special characters, it must be surrounded by square brackets. For ordinary table names and field names, the brackets are optional.

& is used to concatenate text string.

In VBA code, the underscore _ at the end of a line is used as the line continuation character.

 

[ASharifs]

Thanks. Appreciated ..
A.