How to export all tables to the csv files when I open the database 1

[Ankor]

Hello all!
I am very new to Access and VBA coding, but I think it should be possible to do what I need. I would like to be able to open Access, and on the open event it should go through all tables I have and create a .csv file for each of them with the same name as the table has. Then Access should close.
I am pretty sure I can do it in Excel (I have a little bit more experience with it), but I am lost in Access. Could anybody help me?

Thanks in advance.
Ankor

 

[lestatdelioncourt]

this works buty the format is not great:

sub export()

dim dbs as database
dim t as tabledef

set dbs as currentdb

for each t in dbs.tabledefs
if t.fields.count > 0 then
docmd.outputto acoutputtable, t.name, acformattxt, t.name & ".txt", false
end if
next t

end sub

just call this on entry to the db and all tables will be exported including the system tables

 

[Ankor]

Hello lestatdelioncourt,

I have couple more questions. First, when I copied the code I got the line set dbs as currentdb in red. The error message said "Expected =" instead of "as". I changed it, but now when I try to run the Sub, I am getting another message: on the line dim dbs as database it says "User-defined type not defined".

Also, how do you call a sub on entry.

I appreciate your help. Ankor

 

[lestatdelioncourt]

sorry yes it should be:

set dbs = currentdb

the other problems are because the code needs to be in a module not on a form.

The easiest way to get the code to run on start up, is you can set a default form:

tools/startup

then specify the form name.

the code in the form will be:

Private Sub Form_Load()
DoCmd.SetWarnings False
export
application.CloseCurrentDatabase

End Sub

this will open the DB export the tables and then close.

 

[Ankor]

I have my code in the Module, I don't even have forms created in the database, but it still does not work. When I try to Dim something, the help list does not show anything like "Database" or "Tabledef". Should I open some kind of additional library?

 

[lestatdelioncourt]

sorry again this works in access 97.

in access 2000 you need to add areference to:

microsoft dao 3.6 object library.

to do this, in module design. click tools/reference and selct it

 

[Ankor]

This perfectly works!!! Thank you!