How to append multiple linked tables from one DB to another

[Guest]

How to append multiple linked tables from one DB to another empty one with
same structure?

I am trying to move certain records and their corresponding links to other
tables into a new database using the unique ID I created of type text. I
orginally created 8 queries, one for each table, using the main table in the
query to get the desired record and getting input from the user what record
they want to move over using the unique ID called CaseNum (text field:
06-1234). I tried to combine them in a macro so I dont have to manually
click each append query and run it, however I found that if one of the values
in the old table is empty or Null it causes and error which Halts the macro
and thus doesn't run the remaining append queries. If all the information is
there it asks me for the CaseNum 8 times. I wish I could have it only ask me
once but I can't seem to get that to happen, so for now how do I get it to
skip the null ones and continue with the rest of the macro.

Thank you in advance

 

[Wolfgang Kais]

Hello Underpaidadmin.

How to append multiple linked tables from one DB to another empty
one with same structure?

I am trying to move certain records and their corresponding links to
other tables into a new database using the unique ID I created of type
text. I orginally created 8 queries, one for each table, using the main
table in the query to get the desired record and getting input from the
user what record they want to move over using the unique ID called
CaseNum (text field: 06-1234). I tried to combine them in a macro
so I dont have to manually click each append query and run it, however
I found that if one of the values in the old table is empty or Null it
causes an error which Halts the macro and thus doesn't run the
remaining append queries.

If there was a restriction in the tables you are appending to, why is there
no restriction in the original tables?
If you only want to append records that don't contain Null in certain
fields, use criteria in the append queries.

If all the information is there it asks me for the CaseNum 8 times.
I wish I could have it only ask me once but I can't seem to get that to
happen, so for now how do I get it to skip the null ones and continue
with the rest of the macro.

I think you should run the 8 queries from a VBA procedure.
Using DAO (Data Access Objects), there is a QueryDef object for every query
stored in the database. These have an Execute method. Before executing the
queries, you can set the values for the parameters (you must have declared
the parameters in query design view using the Parameters command from the
Query menu). The procedure could ask the user for the value of CaseNum only
once and store the value in a string variable that is then used to set the
values for the parameters of the QueryDefs before executing them.

If you want to use a macro, I suggest to start the macro from the OnClick
event of a command button in a form. The user should select a valid CaseNum
in a combo box and the queries should use the value of that ComboBox for the
criteria.

 

[Guest]

I wasn't sure how to go about this but I like it better than macros so I was
searching around and found
this(http://www.fmsinc.com/tpapers/queries/index.html#Parameters):

Private Sub RunAQuery (strQueryName As String)
' Input : strQueryName Name of saved query to run
Dim db As Database
Dim qry As QueryDef
Set db = CurrentDB()
Set qry = db.OpenQueryDef(strQueryName)
DoCmd SetWarnings False
qry.Execute
DoCmd SetWarnings True
qry.Close
db.Close
DoEvents
DBEngine.Idle
End Sub

I don't understand everything there or why we need to do db.Close or
qry.Close, but I also don't see how you could request a parameter from a user
once and use it in the either queries. Perhaps you could elaborate on that
part please?

 

[Wolfgang Kais]

Hello Underpaidadmin.

I wasn't sure how to go about this but I like it better than macros so
I was searching around and found
this (http://www.fmsinc.com/tpapers/queries/index.html#Parameters):

Private Sub RunAQuery (strQueryName As String)
' Input : strQueryName Name of saved query to run
Dim db As Database
Dim qry As QueryDef
Set db = CurrentDB()
Set qry = db.OpenQueryDef(strQueryName)
DoCmd SetWarnings False
qry.Execute
DoCmd SetWarnings True
qry.Close
db.Close
DoEvents
DBEngine.Idle
End Sub

I don't understand everything there or why we need to do db.Close
or qry.Close, but I also don't see how you could request a parameter
from a user once and use it in the either queries. Perhaps you could
elaborate on that part please?

Ok.
First: you forgot the code for the parameter.
Second: CurrentDB does not have to be closed.
Third: OpenQueryDef must be from a very old version of Access.
I suggest something like the following:

Sub RunAll8Querys()
Dim dbs As Database
Dim qdf As QueryDef
Dim strCaseNum as String

strCaseNum = InputBox("Enter the case number:","Transfer","")
Set dbs = CurrentDB
Set qdf = dbs.QueryDefs("NameOfQuery1")
qdf.Parameters("NameOfParameter") = strCaseNum
qdf.Execute
qdf.Close

' Second query
Set qdf = dbs.QueryDefs("NameOfQuery2")
qdf.Parameters("NameOfParameter") = strCaseNum
qdf.Execute
qdf.Close

' do this for all 8 queries, i'll not write down similar code 8 times.

Set qdf = Nothing
Set dbs = Nothing
End Sub

A QueryDef has to be closed just as you close the query design window.
The InputBox function asks the user to enter a character string.
The entered value is stored in the strCaseNum variable.
This variable is used to specify the value for the parameter of each query.

Alternatively, use a form with a combo box so the user can select the
CaseNum to process. Add a button to the form that executes the code. In this
scenario, omit the InputBox function and the strCaseNum variable and use the
name of the combobox instead of the name of the variable in:
qdf.Parameters("NameOfParameter") = Me.NameOfComboBox

I hope this was a little more clear.

 

[Guest]

WOO thank you so much that looks exactly like what I need to do. There is
just one more problem (Sorry). When I run it I get Run-Time error (3162):
You tried to assign the Null value to a variable that is not a variant data
type. This only happens when the field is empty in the old table. Do I need
to make some sort of Null check? I am going to try that anyway but if that
is the wrong way to go please tell me. Again thank you so much that helped
incredibly.

 

[Guest]

I just found out I can't make a null check because the queries copy the
entire table using the *.[TableName] function? so I am wondering would it be
easier to change the queries so that they copy each field from each table
serpately and throw a Not IsNull criteria in every single field or there is
some other simpler or more sensible way to do it. Sorry if I seem so
clueless, because for the most part I am . I will also post an example of
one of the queries:

INSERT INTO tblAcctNumslst IN 'J:\Security\Security Case Files\Security3.mdb'
SELECT tblAcctNumslst.*
FROM tblCasesMain INNER JOIN tblAcctNumslst ON tblCasesMain.CaseNum =
tblAcctNumslst.CaseNum
WHERE (((tblCasesMain.CaseNum)=[Enter New Case Number]));

 

[Wolfgang Kais]

Hello Underpaidadmin.

I just found out I can't make a null check because the queries copy
the entire table using the *.[TableName] function? so I am wondering
would it be easier to change the queries so that they copy each field
from each table serpately and throw a Not IsNull criteria in every single
field or there is some other simpler or more sensible way to do it.
Sorry if I seem so clueless, because for the most part I am .
I will also post an example of one of the queries:

INSERT INTO tblAcctNumslst IN 'J:\Security\Security Case
Files\Security3.mdb'
SELECT tblAcctNumslst.*
FROM tblCasesMain INNER JOIN tblAcctNumslst ON tblCasesMain.CaseNum =
tblAcctNumslst.CaseNum
WHERE (((tblCasesMain.CaseNum)=[Enter New Case Number]));

Be sure you have declared the parameter!
If you would have declared it, the SQL of the query began with something
like
PARAMETERS [Enter New Case Number] Text ( 255 );
(depending of the data type of the parameter)

You can easily add a criterion for a field by adding the field to the list
and
clearing (if automatically filled) the name of the target field.

Are you sure that you want to link two tables? Why not use this:
PARAMETERS [Enter New Case Number] Text ( 255 );
INSERT INTO tblAcctNumslst IN 'J:\Security\Security Case
Files\Security3.mdb'
SELECT * FROM tblAcctNumslst
WHERE CaseNum=[Enter New Case Number] And MyOtherField Is Not Null;

The "parameter"-line of code for this query should read something alike:
qdf.Parameters("Enter New Case Number") = strCaseNum

 

[Guest]

Well I'll be... Seems just adding that little parameter thing solved the null
check problem. I am a little curious as to why but not overly. You have
helped me tremendously, I thank you very much. I have one other question but
I will post it to a seperate post.

Once again thank you so much!

Hello Underpaidadmin.

I just found out I can't make a null check because the queries copy
the entire table using the *.[TableName] function? so I am wondering
would it be easier to change the queries so that they copy each field
from each table serpately and throw a Not IsNull criteria in every single
field or there is some other simpler or more sensible way to do it.
Sorry if I seem so clueless, because for the most part I am .
I will also post an example of one of the queries:

INSERT INTO tblAcctNumslst IN 'J:\Security\Security Case
Files\Security3.mdb'
SELECT tblAcctNumslst.*
FROM tblCasesMain INNER JOIN tblAcctNumslst ON tblCasesMain.CaseNum =
tblAcctNumslst.CaseNum
WHERE (((tblCasesMain.CaseNum)=[Enter New Case Number]));

Be sure you have declared the parameter!
If you would have declared it, the SQL of the query began with something
like
PARAMETERS [Enter New Case Number] Text ( 255 );
(depending of the data type of the parameter)

You can easily add a criterion for a field by adding the field to the list
and
clearing (if automatically filled) the name of the target field.

Are you sure that you want to link two tables? Why not use this:
PARAMETERS [Enter New Case Number] Text ( 255 );
INSERT INTO tblAcctNumslst IN 'J:\Security\Security Case
Files\Security3.mdb'
SELECT * FROM tblAcctNumslst
WHERE CaseNum=[Enter New Case Number] And MyOtherField Is Not Null;

The "parameter"-line of code for this query should read something alike:
qdf.Parameters("Enter New Case Number") = strCaseNum