Help with Dir function

[KrazyKT]

I currently have a module that will export five tables to text. The tables have to be in the mdb I'm using. I know Dir will call all files in a directory. How do I go about either importing them into one mdb file with the same five tables or just opening them and exporting their tables? Thanks,

KT [sig][/sig]

 

[MichaelRed]

KrazyKT,

The following is VERY rough. It not just can be - it is hazzardous. It is intended ONLY as something to kick start the mental process.


Public Function MultiMDB_Open()


Dim Wks As Workspace
Dim Dbs As Database
Dim DbNew As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim rst4 As Recordset
Dim rst5 As Recordset
Dim MyMDB As String
Dim MyPath As String

Set Wks = DBEngine.Workspaces(0)
Set Dbs = CurrentDb

MyPath = "C:\My Documents\"

MyMDB = Dir(MyPath & "*.MDB&quot

While MyMDB <> &quot;&quot;
Set DbNew = Wks.OpenDatabase(MyMDB) 'parameters may be required here

Set rst1 = dvnew.OpenRecordset(&quot;tablename1&quot;, dbOpenDynaset)
Set rst2 = dvnew.OpenRecordset(&quot;tablename2&quot;, dbOpenDynaset)
Set rst3 = dvnew.OpenRecordset(&quot;tablename3&quot;, dbOpenDynaset)
Set rst4 = dvnew.OpenRecordset(&quot;tablename4&quot;, dbOpenDynaset)
Set rst5 = dvnew.OpenRecordset(&quot;tablename5&quot;, dbOpenDynaset)
'Do the specific processing for the single database here



'Set 'foregin db objects to nothing, so they may be re-used
Set rst1 = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set rst4 = Nothing
Set rst5 = Nothing
Set DbNew = Nothing

MyDb = Dir

End Function
[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[KrazyKT]

Michael,

Thanks for the help. I managed to get it to read the files in the directory. My question now is..Can you export the tables to text? I know it has to be within the loop. Also can I do this without having to use the fields? I'm wanting to export them to fixed width files.

KT [sig][/sig]

 

[MichaelRed]

KrazyKT,

It is reasonably easy to export the table contents to a text file with the 'TransferText' action/method. Unfortunately, to do the fixed width export, you need to create the inport/export specification, which is a bit difficult form the academic perspective. It is not hard - within Ms. Access, so you can/should at least attempt it. If/When you are able to create the specification, be sure to name & save it (and REMBER/Note the name). Just look at the transfertext method, you should be able to work out the various arguments and get this working. The only issue I see from your posts is to make sure you have a naming convention for your exported files (fielname in the helpfile information which will not overwrite previous entries.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[KrazyKT]

Thanks. We have started using that method. It appears that the text files are overwriting one another. How would you create a file to change with every new
table? That is the problem we are having now. I will read up on the filename in help.
Thanks for the help. Much appreciated.

KT [sig][/sig]

 

[MichaelRed]

KrazyKT,

A method for the file naming is to transpose the input names to the output names. Use whatever part of the input is unique to form the output. Modify the output name to reflect the transposition of the file. As an example, you are getting Table1 from MyDb1.MDB on DateA, a vialble output filename would be MyDb1Tbl1_MM-DD-YY.Txt as an output filename.

This scheme can geenerate a long name, but it DOES give the pertinint info, in what should be a unique naming convnetion. If not, I'm sure you can see the pattern for the process and generate your own scheme to suit. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]