Jet error 3011 when opening a file with 2 "extensions"

Question

I'm having a problem with Jet throwing error 3011 when I try to use it to open a file with 2 "extensions" ("filename.tst.csv").

Run-time error '3011'

The Microsoft Jet database engine could not find the object 'filename.tst.csv'. Make sure the object exists and that you spell its name and the path name correctly.

Where the code looks like this:

Dim db as Database, rs as Recordset
Set db = OpenDatabase("SELECT TOP 1 * FROM [" & fileName & "];")
Set rs = db.OpenRecordset("SELECT TOP 1 * FROM [" & fileName & "];")

I've tracked down the problem to be the second extension (or apparent extension) ".tst" but I'm not sure why the error is occurring and I haven't been able to find an answer through Google or through here - but I figure someone might have some insight as to the limitations.

This is using DAO 3.6 in VB6.

Answer 1

It's a bit hackey, but you could programatically rename the file before opening it to a more friendly extension. Just keep the old extension around so you can set it back when you are done.

Answer 2

This isn't exactly the situation you're talking about, but in reading this, in the CAUSE section, the information about some drivers not supporting long filenames looks like it would apply, since this is not in an 8.3 format.

http://social.technet.microsoft.com/Search/en-US?query=3011&ac=8

So, you'd need to rename the file OR use another method to load the data (such as using a StreamReader). Personally, I'd rename the file. It'd be much easier.

Answer 3

You need to either create a schema.ini file for your import file, or add the extension to the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format registry key so ADO knows how to parse the file. There is a detailed explanation at http://msdn.microsoft.com/en-us/library/ms974559.aspx

Answer 4

Why not just use the short file name instead? It won't contain more than one period. Just drop in the CFileInfo class from this offering from Karl E Peterson's excellent site (it has a wrapper method for the GetShortPathName API call).

I suppose the volume might not support short file names (it's optional).

Answer 5

I'm not certain about this because I haven't used DAO much since it became obsolete in VB6. I know that Jet's rules can be very different going between the OLE DB Provider, ODBC Driver, and DAO.

Have you tried replacing the "." characters in those file names by "#" characters? Or maybe just the last one? Just in your SQL statement text.

Answer 6

According to me, your second line of code cannot return anything!

Set db = OpenDatabase("SELECT TOP 1 * FROM [" & fileName & "];")

You cannot open a database with a SELECT instruction! Your code shouls look like

Set db = DBEngine.Workspaces(0).OpenDatabase(filenamne)