Improving an Access database

[dbrooks]

I am having a problem improving my company's Access database.&nbsp;&nbsp;It is basically a flat file database that has a lot of manual processes and my boss wants this database to be completely automated.&nbsp;&nbsp;Here are some obstacles I am running into to automate this database.&nbsp;&nbsp;<br><br>First, I receive a *.dat file from HQ.&nbsp;&nbsp;Then I have to open the file in Excel, delete the headers off the file (sometimes) and rename the file to a 'txt' extension.&nbsp;&nbsp;Finally, I import the file in it's appropriate table.<br><br>Here are problems with creating macros for this process.<br><br>- different headers for big files<br>&nbsp;&nbsp;- if I change the table headers then we have many queries to change<br>- create many macros for many tables<br>- there is a different file name to import everyday.&nbsp;&nbsp;(e.g. sw_actsdetl_20000731_028341.dat, sw_actsdetl_20000801_293841.dat)<br>- can't automate import w/ VB6 because HQ only has FTP explorer & WS_FTP as their FTP shell.&nbsp;&nbsp;Which means that I can't use DOS to download my files.<br><br>If anyone could give me any suggestions or helpful hints I would surely appreciate it.<br><br>

 

[DougP]

Don't know what using VB6 and WS_FTP has to do with DOS?/!<br>Is the .DAT file on a WEB site that you have to go get?<br>Have you tried importing directly into Access and leave Excel out of it.<br>You need to create an Import Specifcation file in Access<br>Which you can call below.<br><br>Public Static Function AutomateMe() As String<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.TransferText acImportDelim, &quot;SpecificationName&quot;, &quot;YourNewTable&quot;, &quot;C:\YourTextFile&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset, SQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(&quot;YourNewTable&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Delete<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.MoveFirst&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'no this is not a typo, if you delete the first record the then second one becomes the first.<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Delete&nbsp;&nbsp;&nbsp;&nbsp;'put as many of these as you need.<br>End Function<br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.