Exporting and importing data

[odrap]

Using the ms access 2007 i realized an export of the data of a datatable into a semicolumn delimited textfile and an import of this text file back into a ms access datatable.
Now i want to do the same with ms access- or vba code, and therefore i would like to know where i can find the code the wizards made to fullfill this export and import in order to use it in my own code so that by clicking a button on a form can make it possible to realise such an import or export without the need of the import or export wizard.

 

[gemma-the-husky]

docmd.transfertext does a csv or other text file
docmd.transferspreadsheet does a xls directly

 

[JohnLee]

Hi,

I would like to add here, that you will need to create an import/export specification before you write any VB code because you will need to reference the specification in your code. I would suggest you use the import/export wizard to assist you with the creation of your specifications, otherwise you may not get the desired results.

John

 

[wiklendt]

just a quick question: i am making a(nother LOL) database for work. this database will need to be distributed to a few people around sydney so they can enter data of their specimens/tests. (DB won't be on a common network - though we've not explored SQL/online methods b/c i have not the expertise there.)

what we had in mind is that this data would be exported in the off-site database, sent to us (csv? txt? xls? not decided yet) and imported at our end into what will essentially be the same database, but will also contain OUR data, and the data from the other places around sydney, and PAST data too.

this database is normalised with MANY tables.

with the DoCmd.TransferText/TransferSpreadsheet - is Access 'smart' enough to 'normalise' the file on import? i.e., all instances of the database are essentially identical, save for the actual data they hold, so all field names will match perfectly.

...or am i barking u pthe wrong tree...?

 

[JohnLee]

Hi,

Providing the tables in all the databases match exactly, that is field names exactly the same field properties exactly the same, you can link all the databases and then do the following:

1. Produce queries in your database that appends the data from their databases to your tables.
2. Write code that runs these queries in sequence.
3. Attached the code to a button on a form that when clicked automatically appends the data to your database.

John

 

[wiklendt]

Thanks JohnLee, that's a method i'd not thought of, which could be relatively simple. i've never ever in my life linked databases together, so hopefully this won't be too much a steep learning curve... but seeing as my databases are identical (structure/FieldName-wise) this could be a good solution without much hassle.

my boss is keen to keep my 'database time/complexity' to a minimum (he's a very smart man), seeing as my actual job role is something completely different (molecular biology), so he's very accepting to minimise bells and whistles - something of a rarity for people on this forum, from what i've read! LOL

 

[JohnLee]

Hi WIKLENDT,

To Link to tables in another database from the drop down menue choose:

File
Get External Data
Link Tables

At this point the Link window will pop up for you to navigate to the database whose tables you want to link to. On selecting the required database another window will pop up for you to select all the tables you want to link to, you can do this one at a time or by selecting each table you want to link to and then clicking the "OK" button or if you want to link to all of the tables in that database choose the "Select all" button. Then a link to those tables will be automatically made.

You need to note at this point because you will have tables in your destination database with the same name, what access will do is add a number to the end of the linked table, for instance a table in your destination database with the name of tblMyTable will have for the linked table tblMyTable1 and so on for each different link to each database you link to i.e. tblMyTable2, tblMyTable3 etc

You can identify linked tables because they will have a right pionting arrow to the left of the table object.

I would suggest that once you have linked to the desired tables in each of the databases that you rename your linked tables to help you identify which database they are linked to, for instance:

tblMyTable [the table in your destination database]
tblMyTable1 rename as tblMyTable_Victoria
tblMyTable2 rename as tblMyTable_Sydney

and so on for each database you link to. This will help considerably when you come to creating your append queries, because you will be able to easily identify where you are appending data from to your destination table to your destination database.

This is how I would approach it, and there may be others who might do things differently, which might appeal to you more than my approach, anyway I hope this will go some way to helping you get the solution you want, I'm a happy to assist further if I can.

John

 

[odrap]

Thank you for the help

 

[JohnLee]

Hi Odrap,

Your most welcome

John