Exporting table to .csv file, then importing .csv file back to tab

[Guest]

I’m sure that this task is rather easy really, but when you haven’t got the
first clue anything can seem hard!

I have a simple database that is going to be used in conjunction with a
‘batch terminal’ type barcode scanner for stock control purposes.

I have used the wizard within MS Access 2000 to export to a csv file, and I
have managed to make it work as desired with my barcode scanner. Also, I have
managed to import it (although this was slightly trickier).

What I ideally need is a way (macro perhaps?) to export the entire table to
the .csv file (to a predefined location, with a predefined name) without
having to use the wizard, but instead just by clicking an item from the
switchboard.

Also, I need a similar way to get the new data back into the database,
bearing in mind that the imported data will need to overwrite the existing
table contents.

Any help with this would be gratefully received. Please be as specific as
possible, as I’m only just learning the whole database thing! I think at some
point soon I’m going to have to really study SQL and VB…

Thanks in anticipation.

Nick.

 

[John Nurick]

Hi Nick,

To export the data in a table to a CSV file in a fixed location, just
use

DoCmd.TransferText acExportDelim, , "MyTable", _
"D:\Folder\File.txt", -1

To re-import, just delete all the records from the table and use
TransferText again

CurrentDB.Execute "DELETE * FROM MyTable;", dbFailOnError
DoCmd.TransferText acImportDelim, , "MyTable", _
"D:\Folder\File.txt", -1

There are lots of possible complications, especially if you are using
relational integrity.

 

[Guest]

Hi John,

Thanks for your reply, I can make sense of the code, and understand what I
need to change to make it work with my database, but as I said I am very new
to this...

This probably sounds totally stupid, but where do I type this code in? Is it
somewhere in the macro screen?

Sorry for being dumb, but I guess everyone started somewhere?

Nick.

 

[John Nurick]

Hi Nick,

Go to the VB Editor (the quickest way is to hit Ctrl-G). On the Tools
menu, select Options, and on the Edit tab make sure that all the items
under "Code Settings" are checked.

Then use the Insert menu to insert a new module. In the module, below
the
Option Database
Option Explicit
type something like this. Note the use of constants for the file name
and table name so that if they ever change you only have to alter one
line of code, not go hunting for several lines.

Const FILE_SPEC As String = "D:\Folder\File.txt"
Const TABLE_NAME As String = "MyTable"

Public Sub ExportToTheCSVFile()

DoCmd.TransferText acExportDelim, , TABLE_NAME, _
FILE_SPEC, -1

End Sub


Public Sub ReImportFromTheCSVFile()

CurrentDB.Execute "DELETE * FROM " & TABLE_NAME & ";", dbFailOnError
DoCmd.TransferText acImportDelim, , TABLE_NAME, _
FILE_SPEC, -1

End Sub



You can then use the "Run Code" option in the Switchboard Manager to
connect the VBA procedures to switchboard buttons.

 

[Guest]

Cheers John,

That's done the trick a treat.

I've quite enjoyed this little project, I feel that I ought to have a proper
go at learning this VB stuff soon though, whilst my interest is still running
high.

Thanks once again.

Nick.

 

[John Nurick]

It's worth it. There are a zillion things you can't do any other way.