An ADO data source string can contain modifiers. This means that there are
two sets of semicolons within a connection string. One set delimits property settings (Provider, Data Source, UID, PWD, Extended Properties, Initial Catalog, etc.). The other set delimits "modifiers" within the value string for each property.
Ok, that's clear as mud.
It doesn't help that ADO will try hard to help you out, parsing the connection string and taking guesses at which are "major" semicolons and which just set off modifiers. This means one string works fine, another string looks like it ought to work too but it doesn't.
You can be more explicit by using quotes within your connection strings. This
might work just fine.
Fig 1.
Code:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb;PWD=1234
This may not:
Fig 2.
Code:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=my test.mdb;PWD=1234
And if your Data Source string is complex, like a full path and file name, you can really start having troubles. Instead try:
Fig 3.
Code:
Provider="Microsoft.Jet.OLEDB.4.0";Data Source="my test.mdb";PWD="1234";
I quoted the property value strings, and I added that redundant-seeming semicolon at the end too. It doesn't hurt, I feel it shouldn't be needed, but MS uses a lot of C developers who haven't a clue that semicolons are
separators rather than
terminators in programming languages here on Planet Earth.
You might even find that your code would work fine if you had a UID value assignment in there. Sometimes things like that help "clue in" ADO about what it is playing with when it does its double-scan of connection strings trying to assign semicolon levels.
Ok, that "semicolon level" bit. I need to explain that more.
Fig 4.
Code:
Provider="Microsoft.Jet.OLEDB.4.0";Data Source="my test.xls";
Extended Properties="Excel 8.0;HDR=No";PWD="1234";
See how this example has a property setting for Extended Properties, and how there are
two semicolon-delimited substrings in the value string? The semicolon before the "HDR=No" part is one of these
second level semicolons. Just a note here: if you put a semicolon after the "No" and before the quote it will die on an ADO error. Weird and inconsistent I know.
The point of all of this is... I think that ADO and/or the Jet OLEDB Provider are getting confused and are passing something strange to that part of Jet that handles opening the data source. It doesn't look like you want an MDB, so the software scans for an installable ISAM module for an external data source type and fails to find a match.
I suggest you try:
Fig 5.a.
Code:
strconn="Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""" & _
Server.MapPath("test.mdb") & """;PWD=""1234"";"
Or:
Fig 5.b.
Code:
strconn="Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & _
Server.MapPath("test.mdb") & "';PWD='1234';"
Please note that (5.a.) is
preferable because the apostrophe (') is a valid file name character, and one more likely to be used than a quote ("
. In theory the odds that Server.MapPath( ) will return a string with an apostrophe in it (and break your connection string) are higher than that a quote will be in the returned value.
To be really safe, you ought to use
Code:
Replace(Server.MapPath("test.mdb"), """", """""")
to double-up the quotes in (5.a.). You could also use
Code:
Replace(Server.MapPath("test.mdb"), "'", "''")
with (5.b.) instead.
In your particular case you probably got burned by a
blank space before the phrase Data Source. That means you might get away with:
Fig 6.
Code:
strconn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath("test.mdb") & ";PWD=1234"
In other words just deleting the blank space.
The moral of the story is that you must be explicit about what you want, or the string will be parsed twice and rules that aren't clear will be applied to figure out what fits where in the property hierarchy.
The specific error you got almost always means you have messed up your connection string in some subtle way.
