importing relationships with DoCmd

[Chrissy]

I import tables in VBA using "DoCmd.TransferDatabase acImport, "Microsoft
Access", "f:\01 PetsAtlanta TEST\TEST petsatlanta_be.mdb", acTable, "tbl 1
Client", "tbl 1 Client", True"

This works great, but the relationships are not included. Is there a switch
for that? Another method?

I am amateur in need of guidance -- and it is very much appreciated.


Thanks so much,

 

[Wayne-I-M]

Hi Chrissy

This is a copy of an answer I gave while ago to the same sort of problem.

Hope it helps




********************************

To import data from a table in an external database into (append) to a table
in your internal database.


This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are
deleted
after the end is accomplished?)


Names – In this I have used….


External Database - This is where the records are now
The records are in a table called tblOldExport


Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.


2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend


3 Past the same table again (structure only) Call it tblInternalNewRecords


So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.


4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.


5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)


6 Create a new form in Internal Database.


7 Create a new button (call it cboImport)


8 Put this code on the OnClick event


Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " tblOldExport ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub


Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee


Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).


Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.


I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like

[TimeDateField] in the criteria row.

Good luck

 

[Chrissy]

Wayne, thanks for the response. At the core of your method is the
DoCmd.TransferDatabase...etc. I am already that far.

I am able to import, scrub, update, etc., resulting in good data in the
database being built.

My issue is limited to the relationships. I don't wish to keep rebuilding
them during developement. (I am nearing the end, but will have many more
iterations of use of DoCmd.TransferDatabase.)

I want to continue to use DoCmd.TransferDatabase unless there is compelling
reason not to -- I just wish to avoid the relationship reestablishment each
time I transfer live data.

Attempts to find a DoCmd switch or some such have been in vain. Hope this
clarifies....still needing help.

--
Chrissy

Hi Chrissy

This is a copy of an answer I gave while ago to the same sort of problem.

Hope it helps




********************************

To import data from a table in an external database into (append) to a table
in your internal database.


This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are
deleted
after the end is accomplished?)


Names – In this I have used….


External Database - This is where the records are now
The records are in a table called tblOldExport


Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.


2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend


3 Past the same table again (structure only) Call it tblInternalNewRecords


So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.


4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.


5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)


6 Create a new form in Internal Database.


7 Create a new button (call it cboImport)


8 Put this code on the OnClick event


Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " tblOldExport ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub


Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee


Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).


Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.


I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like

[TimeDateField] in the criteria row.

Good luck


--
Wayne
Manchester, England.

I import tables in VBA using "DoCmd.TransferDatabase acImport, "Microsoft
Access", "f:\01 PetsAtlanta TEST\TEST petsatlanta_be.mdb", acTable, "tbl 1
Client", "tbl 1 Client", True"

This works great, but the relationships are not included. Is there a switch
for that? Another method?

I am amateur in need of guidance -- and it is very much appreciated.


Thanks so much,

 

[Wayne-I-M]

Hi

I am not really sure what you are trying to do but (in my small experience)
there are veyr times when you actually need to import a table full of data
and insert it into another DB. What I find, most of the time, is that it is
the date "in" the table that you need - not the table itself.

With this in mind I think you should maybe forget about import a table (an
in doing so mess up your relationships). Of course there are way to brin in
a table and maintain the relationships but these are quite complex - so best
just leave them alone.

Best bet would be to create the DB as you need it with all the
relationships, forms, querys and other "stuff". Then just import and append
an data you need. If you want this new data to take the place of some that
is already there then just run a delete query (just before the append) an
this will work fine.


--
Wayne
Manchester, England.

Wayne, thanks for the response. At the core of your method is the
DoCmd.TransferDatabase...etc. I am already that far.

I am able to import, scrub, update, etc., resulting in good data in the
database being built.

My issue is limited to the relationships. I don't wish to keep rebuilding
them during developement. (I am nearing the end, but will have many more
iterations of use of DoCmd.TransferDatabase.)

I want to continue to use DoCmd.TransferDatabase unless there is compelling
reason not to -- I just wish to avoid the relationship reestablishment each
time I transfer live data.

Attempts to find a DoCmd switch or some such have been in vain. Hope this
clarifies....still needing help.

--
Chrissy

Hi Chrissy

This is a copy of an answer I gave while ago to the same sort of problem.

Hope it helps




********************************

To import data from a table in an external database into (append) to a table
in your internal database.


This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are
deleted
after the end is accomplished?)


Names – In this I have used….


External Database - This is where the records are now
The records are in a table called tblOldExport


Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.


2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend


3 Past the same table again (structure only) Call it tblInternalNewRecords


So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.


4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.


5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)


6 Create a new form in Internal Database.


7 Create a new button (call it cboImport)


8 Put this code on the OnClick event


Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " tblOldExport ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub


Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee


Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).


Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.


I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like

[TimeDateField] in the criteria row.

Good luck


--
Wayne
Manchester, England.

I import tables in VBA using "DoCmd.TransferDatabase acImport, "Microsoft
Access", "f:\01 PetsAtlanta TEST\TEST petsatlanta_be.mdb", acTable, "tbl 1
Client", "tbl 1 Client", True"

This works great, but the relationships are not included. Is there a switch
for that? Another method?

I am amateur in need of guidance -- and it is very much appreciated.


Thanks so much,

 

[Wayne-I-M]

Hi

Not ure if you are looking for the number to be filled in auto - in this
case create an autonumber field in the table and in a query use something like

SomeName: Format(Date(),"yyyy") & " - " &
IIf([TableName]![AutoNumberField]<10,"00" &
[TableName]![AutoNumberField],IIf([TableName]![AutoNumberField]>9 And
[TableName]![AutoNumberField]<100,0 &
[TableName]![AutoNumberField],[TableName]![AutoNumberField]))

This assumes you are using the current year to give the first "bit" of the
string - oh as you will notice the vast majority of tis will become redundant
after the autonumber goes over 100 - in this case you could just use

SomeName: Format(Date(),"yyyy") & " - " & [TableName]![AutoNumberField]

If you are inputting the 2008 then just use (almost) the same as above but
alter it to use the name of the control you are putting the 2008 into instead
of the [TableName]![AutoNumberField]

=Format(Date(),"yyyy") & " - " &[ControlName]



--
Wayne
Manchester, England.

Wayne, thanks for the response. At the core of your method is the
DoCmd.TransferDatabase...etc. I am already that far.

I am able to import, scrub, update, etc., resulting in good data in the
database being built.

My issue is limited to the relationships. I don't wish to keep rebuilding
them during developement. (I am nearing the end, but will have many more
iterations of use of DoCmd.TransferDatabase.)

I want to continue to use DoCmd.TransferDatabase unless there is compelling
reason not to -- I just wish to avoid the relationship reestablishment each
time I transfer live data.

Attempts to find a DoCmd switch or some such have been in vain. Hope this
clarifies....still needing help.

--
Chrissy

Hi Chrissy

This is a copy of an answer I gave while ago to the same sort of problem.

Hope it helps




********************************

To import data from a table in an external database into (append) to a table
in your internal database.


This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are
deleted
after the end is accomplished?)


Names – In this I have used….


External Database - This is where the records are now
The records are in a table called tblOldExport


Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.


2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend


3 Past the same table again (structure only) Call it tblInternalNewRecords


So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.


4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.


5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)


6 Create a new form in Internal Database.


7 Create a new button (call it cboImport)


8 Put this code on the OnClick event


Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " tblOldExport ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub


Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee


Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).


Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.


I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like

[TimeDateField] in the criteria row.

Good luck


--
Wayne
Manchester, England.

I import tables in VBA using "DoCmd.TransferDatabase acImport, "Microsoft
Access", "f:\01 PetsAtlanta TEST\TEST petsatlanta_be.mdb", acTable, "tbl 1
Client", "tbl 1 Client", True"

This works great, but the relationships are not included. Is there a switch
for that? Another method?

I am amateur in need of guidance -- and it is very much appreciated.


Thanks so much,

 

[Wayne-I-M]

sorry about that - posted in the wrong place

ooops


--
Wayne
Manchester, England.

Hi

Not ure if you are looking for the number to be filled in auto - in this
case create an autonumber field in the table and in a query use something like

SomeName: Format(Date(),"yyyy") & " - " &
IIf([TableName]![AutoNumberField]<10,"00" &
[TableName]![AutoNumberField],IIf([TableName]![AutoNumberField]>9 And
[TableName]![AutoNumberField]<100,0 &
[TableName]![AutoNumberField],[TableName]![AutoNumberField]))

This assumes you are using the current year to give the first "bit" of the
string - oh as you will notice the vast majority of tis will become redundant
after the autonumber goes over 100 - in this case you could just use

SomeName: Format(Date(),"yyyy") & " - " & [TableName]![AutoNumberField]

If you are inputting the 2008 then just use (almost) the same as above but
alter it to use the name of the control you are putting the 2008 into instead
of the [TableName]![AutoNumberField]

=Format(Date(),"yyyy") & " - " &[ControlName]



--
Wayne
Manchester, England.

Wayne, thanks for the response. At the core of your method is the
DoCmd.TransferDatabase...etc. I am already that far.

I am able to import, scrub, update, etc., resulting in good data in the
database being built.

My issue is limited to the relationships. I don't wish to keep rebuilding
them during developement. (I am nearing the end, but will have many more
iterations of use of DoCmd.TransferDatabase.)

I want to continue to use DoCmd.TransferDatabase unless there is compelling
reason not to -- I just wish to avoid the relationship reestablishment each
time I transfer live data.

Attempts to find a DoCmd switch or some such have been in vain. Hope this
clarifies....still needing help.

--
Chrissy

Hi Chrissy

This is a copy of an answer I gave while ago to the same sort of problem.

Hope it helps




********************************

To import data from a table in an external database into (append) to a table
in your internal database.


This is not the only way to do this – there are other methods – the “cheatsâ€
I have given here you can either or not – I use them as I’m lazy and – in
this case – the end justifies the means (especially when the means are
deleted
after the end is accomplished?)


Names – In this I have used….


External Database - This is where the records are now
The records are in a table called tblOldExport


Internal Database - This is where I want them to be
tblInternalNewRecords - This the table in Internal Database where I want
the records go
-----------------------------------------
1 This is the 1st cheat – go to External Database and copy the table
containing the data you want to bring in.


2 Go to Internal Database and paste the table (structure only). Call it
tblImportAppend


3 Past the same table again (structure only) Call it tblInternalNewRecords


So you now have 2 empty copies of the table tblOldExport (but in your
Intenal Database) with new names.


4 Create a new query in your internal database (call it qryImportAppend).
Base this new query on tblImportAppend. Individually click each of the fields
so they all shown in the design grid.


5 Change the query to an Append Query. In the drop down list select
tblInternalNewRecords. If you have copied the tables correctly the fields in
the “Append To†row will fill in automatically. (You can change these update
to fields)


6 Create a new form in Internal Database.


7 Create a new button (call it cboImport)


8 Put this code on the OnClick event


Private Sub cboImport _Click()
Dim WayneIMPfile As TableDef
For Each WayneIMPfile In CurrentDb.TableDefs
If WayneIMPfile.Name = "tblImportAppend " Then
CurrentDb.TableDefs.Delete WayneIMPfile.Name
End If
Next
DoCmd.TransferDatabase acImport, "Microsoft Access", "Path to
ExternalDatabase.mdb", acTable, " tblOldExport ", " tblImportAppend "
DoCmd.SetWarnings False
DoCmd.OpenQuery " qryImportAppend "
DoCmd.SetWarnings True
CurrentDb.TableDefs.Delete "tblImportAppend"
End Sub


Notes on this code
1 Change the names to whatever you want. - I wouldn’t have WayneIMPFile –
unless ….
2 The 1st section just looks for and deletes tblImportAppend if it’s there
3 The next bit DoCmd.TransferDatabase is a standard way to bring in tables
to a DB (press F1 for more information)
4 The path to the MDB can be found by (another cheat).
Start – Run – Browse - Navigate to DB - Select open - Cut the path from the
run box and paste into code - Close run
5 Turn of warning
6 DoCmd.OpenQuery Runs the append query
7 Turn the warnings back on
8 Delete the table the code created (tblImportAppend)
9 End Sub – go and have a coffee


Note – if you look at the design of the Append Query it will look wrong - as
there is no table to base it on. Don’t worry this is normal. The code will
create the table, run the query then delete the table again (I tend to do
this as it’s just extra disk space).


Of course you can append the data to other tables and you don’t need to
append all fields. If you play around with the code and the Append you will
get it to do want you need.


I use this method quite often and have the validation process in the append
query – is text formatted correctly, are numbers right, are postcodes
formated so the post office will acept them, etc, etc. But the most
important validation I use is not importing records that are already in the
table. You can use a time/date stamp (field) in the external table and in
the append simple put something like
[TimeDateField] in the criteria row.


Good luck


--
Wayne
Manchester, England.



:

I import tables in VBA using "DoCmd.TransferDatabase acImport, "Microsoft
Access", "f:\01 PetsAtlanta TEST\TEST petsatlanta_be.mdb", acTable, "tbl 1
Client", "tbl 1 Client", True"

This works great, but the relationships are not included. Is there a switch
for that? Another method?

I am amateur in need of guidance -- and it is very much appreciated.


Thanks so much,