using VB to export tables in Access to text

[KrazyKT]

I have a large quantity of Access files each with four tables in the files. I need an automated way to go into a directory and then export the Access tables as fixed width text files. I know this can be done in VB, I'm just not sure how.

KT [sig][/sig]

 

[swilliams]

You have (at least) two easy methods of doing this:
Method 1
1. create a linked table to the text files (must previously be created, and then you can delete the data) for each table in you want to export.
2. Create an append query to add data from one table to its sister linked table, for each table you want to export
3. in VB, set an object pointing to your database, and then use the execute method of the database object to run the queries.

Method 2 (more difficult, but still quite easy)
1. set an object in VB pointing to your database.
2. loop through each table in turn that you want to export processing each record and formatting as you want and then write out the line to a text file.

If you want any more details on either of these methods, let me know.

Simon [sig][/sig]

 

[KrazyKT]

Simon,

Method 2 sounds wonderful. I believe that would be the most useful to us. Do you have a sample with this or some exerts from a program? Here are a few details on this job. I have a large quantity of mdb files that need to be processed in a daily
turn around. The files will be named according to a six digit number and will vary. The four tables will be always have the same names for each mdb file. (Ex: tblone, tbltwo, tblthree, tblfour). I need to be able to pull the files from a directory and export them into four different fixed text files. I need to be able make the names of the files standard. (Ex: aa(name of mdb file) , bb(name of mdb file), cc(name of mdb file), dd(name of mdb file). Is this possible? Thanks for your help. I have only had an intro level class in this language.

KT [sig][/sig]

 

[swilliams]

All of the above is perfectly possible.
First, create a flow diagram of what you want. ie:
1. loop through a directory getting databases.
2. for each database, loop through the tables
3. for each table, loop through the records, process and write out to file.

Without more info, I can't give you much help with 3., unless I create a dummy database and use this - time consuming (I am still at work!) - but if you don't know how to 1. or 2. (or both) I will help.

Simon [sig][/sig]

 

[KrazyKT]

Simon,

I'm not really sure what I'm doing. It's been about a year and a half since I've programmed anything in Vb. And as they say, if you don't use it, you'll lose it. If you could help, I would appreciate it. This project doesn't start until the 17th. But I will need a few days to train a few people to be able to run the program. So it's not like I need to have it up and running today. My main problem is that finding a generic program is few and far between. I would probably be okay if I had something to go by. Also there is no one that really programs in VB in our office. That is why I'm struggling throught it. If you need any additional info, just ask. I'll do my best. I'm getting desperate.

KT [sig][/sig]

 

[swilliams]

The following code will loop through a directory (hardcoded) looking for mdb's, and then loops through each table in the databases that it finds - still no processing of the records though

Dim db As Database
Dim sDBName As String
Dim sFolderName As String
Dim td As TableDef
Dim rs As Recordset

sFolderName = "C:\vb projects\vb6\adox catalog"
sDBName = Dir(sFolderName & "\*.mdb&quot ' look for mdb's
Do Until sDBName = "" ' if mdb found, sDBName will hold the name of the first db
Set db = Workspaces(0).OpenDatabase(sFolderName & "\" & sDBName, False, False)
For Each td In db.TableDefs
If UCase(Left(td.Name, 4)) <> &quot;MSYS&quot; And td.Attributes <> dbSystemObject Then
Set rs = db.OpenRecordset(td.Name, dbOpenSnapshot)
' open text file here
Do Until rs.EOF
' process each record
' write out line to text file
rs.MoveNext
Loop
rs.Close
' close text file
End If
Next
Set db = Nothing
sDBName = Dir ' move to next database
Loop

To run the code, set a reference to DAO 3.51 in your project.

Simon [sig][/sig]

 

[KrazyKT]

Bless you. I will try this. If I run across any problems, I'll check back in with you. Thanks.

KT [sig][/sig]

 

[KrazyKT]

Simon,

I have a few questions for you. Would this differ any from VB5 to VB6? We are still using VB5. Also what kind of controls do I need? Or do I need any? Also how do you set the reference to DAO 3.51 in the project? I'm sorry for my lack of experience and the questions. It would be nice to have someone around that actually knows what they are doing to help teach me. Thanks for the help again. Also one more question, is there a way you could do this in Access using VBA? Or is it better to stay with VB?


KT

[sig][/sig]

 

[swilliams]

KT,

1. You do not need any controls in VB to do this. Just put the code in the OnLoad event of a form and it will run automatically, or you can put a command button on the form and put the code in the OnClick event of the button, that way, the program will run, but will not process the databases until someone clicks on the button. It is up to you how you run the code - the first way is better if you want to automate the process, as then you don't have to wait until someone clicks a button.
2. To set a reference to DAO 3.51, go to 'Project', 'References' and scroll through the list. Tick the relevant option (Microsoft DAO 3.51 Object Library) and then click 'OK'.
3. You can do this in Access VBA. Personally, I don't like doing this, but there are probably people out there that would rather do this than code it in VB.

4. e mail me at swilliams@paragon.bm

Simon [sig][/sig]