We help IT Professionals succeed at work.

Microsoft Access Linking dat

CFMI
CFMI asked
on
Hello,
Can you please instruct me to link a file with the extension dat into Microsoft Access?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
What produced this file. There are many systems that can create a dat file. Generally, you cannot link directly to them, but need to have them running in their native system first and connect through that.

For instance you can create a dat file from SQL Server - but you would not be able to link to it - you'd have to go back to SQL Server, restore it and them link via SQL Server.


Kelvin
CFMIFinancial Systems Analyst

Author

Commented:
Kelvin,
The dat file is an exported file from the DB2 mainframe.  We normally import it into Excel but the file is too big so I thought I could link into Access instead of importing it.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Presuming the file is a dbase file that MS Access can still read...Try this:

Open Access
Create a new database
Click:
External Data-->Import-->More-->DBase file
Browse to the file
Select: "Link to the data..."
...and see if this works...
query in Access to combine all the data...

If this is a dbase file and Access cannot open, then your best bet may be to split up the data into sizes that Excel can take.
Then import all of the Excel files into Access, then finally run an append query in Access to combine all the data.

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Does this file contain sensitive data?

Can you post it here?
CFMIFinancial Systems Analyst

Author

Commented:
Sorry the file is not a dbase file and it has sensitive data.  Any help is really appreciated as I have to attend a meeting and will check messages, the first thing Thursday morning.  In the meantime, I can try to create a similar file without sensitive data. Have a nice night!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
< Any help is really appreciated>
You have not posted your results of the first two suggestions...
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Sorry...
< Any help is really appreciated>
You have not posted a reply on my first suggestion...
CFMIFinancial Systems Analyst

Author

Commented:
Hello,

I have attached a small text file (AccountsReceivableAging-MHIP.txt) as I had to rename the extension so it would post.  Please rename the extension from txt to dat.  I so hope you can link this file.  Automation is my goal so if linking doesn't work perhaps writing VBA to import the file is a solution.  

Thanks!Public Attachment AccountsReceivableAging-MHIP.txt
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:

The first issue is the file name (extension)
.dat is not recognized by Access 2007.

So you will have to (Somehow) rename the file on the fly (or copy and rename the file on the fly)

It "Looked" like a text file with "|" as the delimiter.
So I renamed the file .txt and tried to link to that.

External Date-->Import-->Text File

Browse to the file and select "Link to the Data..."
Click the Advanced button and and set the delimiter to: |
(The "Pipe" character)

Then check on the box for: First row contains field names

Following these steps I was able to link to the file.
(see attached screenshot)

So try this *First* just to see if you can link to the file.

Again, it seems like you will have to create a system copy and rename the file on the fly to be able to pul this off.
If you need this to be automated you will probably have to use code similar to this:
DoCmd.TransferText acImportDelim, "ImportSpecLinkToDatFile", "TargetTableName", "C:\YourFolder\AccountsReceivableAging-MHIP.txt", True
...but again, just try this manually firs to see if you can at least link to the data manually first.

JeffCoachman
untitled.JPG
CFMIFinancial Systems Analyst

Author

Commented:
Excellent, this works well and I will use VBA to automate the link.  Thanks!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
great

Explore More ContentExplore courses, solutions, and other research materials related to this topic.