Dlookup tab delimited file - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Dlookup tab delimited file

Roger Reece

5 years ago

First of all I know I am trying to make it too difficult and need some help in coming up with a better solution.

We have a form in our access database, where I need to look up a value located in an excel spreadsheet based upon a "ticket number".  If the ticket number is found, the macro returns the test value to the field on the access form; if no match is found, it returns a zero and moves on.

Some background.  We have a piece of test equipment that stores test results in a .tab file.  This piece of equipment is on our network and I can query the results file via an ftp command.  I have set up an excel spreadsheet that has a connection to the .tab delimited ftp file with the properties to update the excel spreadsheet every time the excel file is opened.

In access, I have a form with a field that has an event to run a macro upon update.  This macro refreshes the excel spreadsheet and should look up the data I need from the spreadsheet.

Run Code
  FunctionName RefreshExcel()

SetValue
  Item=[Forms]![Transaction]![FN]
  Expression=DLookup ("[Mean]","[FNTestResults]","
  [LID]="&[Forms]![Transactions]![TicketNo#]

The problem is that if the test equipment is turned off, the access macro stalls since it can not open the ftp file when the "refresh all" command is sent. Even when the test equipment is turned on, the process is very slow and I often have to open and close the excel file to update the spreadsheet.

I have to go through the intermediate step of creating an excel spreadsheet that access can query since I don't believe access can "lookup" values in a .tab delimited file.  The ftp file name is results.tab.  I can not change the file extension to .txt.  I have tried to set properties for opening .tab files to use excel or notepad, but I am not sure that solves anything.

Any suggestions.  Thanks

Roger

	Richard Rost @Reply 5 years ago
If the .tab file is just a tab delimited text file, then you should be able to directly link to it from Access without the need to go thru Excel as an intermediary.

Roger Reece OP @Reply

5 years ago

Thanks for the reply.  The .tab delimited text file is located on a piece of test equipment on our network.  The address is ftp://fn1000_xxxxxx/Results.tab

When I try to set up an Access table with a direct link to the text file which is preferred, I get this error.  "Only XML files can be imported from an internet address (http://, ftp://) Enter a path to a location on your computer or network".

If I use Microsoft Edge to save the file to my desktop, I can then link the access table to the tab delimited file on the desktop, but then the file on the desktop is not updated when a change is made to the master file on the piece of test equipment.  That's the reason I was going the Excel route and linking the the excel file to the master tab delimited file and then in a separate step linking the excel file to the access table.  It seems very complicated for what I am trying to do and am hoping that there is a simpler and better way.

Appreciate any additional thoughts.  Thanks

Roger

	Richard Rost @Reply 5 years ago
You could just make a button in your Access database to perform that FTP for you, grab the file, copy it down locally, and then import it into a table. It's easy to find FTP code for VBA online. I've done it myself before. I'm planning on covering it in a future lesson if you can't figure it out.

	Roger Reece OP @Reply 5 years ago
Richard, Thank you. I'll see if I can find the FTP code online. If you know of one that you have used please send me the link. Do you know which future lesson you plan to cover this in? Roger

	Richard Rost @Reply 5 years ago
Should be easy to find. Just Google "VBA FTP" and the top couple results will probably have something good. Honestly, I haven't done FTP via VBA myself in probably 10 years. I'll try to cover it when I'm done with the current topic which is File I/O... so maybe Developer 33 or 34.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View