dat file opened in Access

[eyetry]

I have a file 'name.dat' that my firm receives as part of an application. A user told me that we don't install the application they just right click on the file and open it with MS Access. Access recognizes the tables and all of the data without having to go thru an import process.

I was able to do what she described. From there I can save the file as 'name.mdb' and automate a read. I can't however just read the original file.

Can anyone tell me what ODBC driver I might use to automate the process, ie.. read the dat file as though it is an Access file?

 

[Remou]

I strongly suspect that the file is an mdb, just renamed. Try using Name As on the datafile to change the extension to mdb and see if that works.

http://lessthandot.com

 

[eyetry]

Sorry, should have said I tried that and it didn't work

Also tried changing the extention to .txt then use the MS Access text driver. Also

 

[Remou]

What does the file look like if you open it with a text editor?

http://lessthandot.com

 

[eyetry]

Good question.....

I just noticed that there is a text string near the begining of the file '...Standard Jet DB...' otherwise, looks a lot like below....


Standard Jet DB
µnb` ÂUé©gr@? œ~Ÿ?ÿ…š1Åyºí0¼ßÌ?cÙäßFûŠ¼N»

 

[Remou]

It is still looking remarkably like an mdb or mde. Does your OS hide the file extensions, so that although it looks like you have renamed, you have just added an extra .ext? if so, you could try renaming at the command prompt:

ren db1.dat db1.mdb

http://lessthandot.com

 

[eyetry]

Tried that too.

 

[Remou]

This is some VBScript. See if it can read out the tables.

Set cat = CreateObject("ADOX.Catalog")
Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=C:\Docs\db1.dat;"
              
Set cat.ActiveConnection = cn

For Each t In cat.Tables
    strList=t.Name & vbcrlf & strList
Next 

MsgBox strList

http://lessthandot.com

 

[eyetry]

Resolved.... I opened the file in Access, saved it as an Access DB. Pointed the ODBC at that file, pointed my tool at that MDB data source, built my definition, deleted the mdb file repointed my ODBC data source to the dat file (as data source) and it started to work fine. Can now extract data from the origial file. Pain but as long as I only had to do it once to properly create my definition....

Thank you for all your help.