Code provider for MS Access instead of ODBC? 1

[Ovatvvon]

I've read that declaring a provider produces better performance than using ODBC where the server will have to look up the information. To declare a provider for sql server you would type:

sql = "provider=sqloledb;User ID=sa;Password=;Initial Catalog=Northwind;Data Source=kirroyal"

But what would you type to declare the provider for Microsoft Access 2000? -Ovatvvon :-Q

 

[link9]

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mapPath("../blahblahblah&quot & ";Persist Security Info=False

Is what my handy dandy UDL tool returned to me.

If you have Win2k, you can right click on the desktop, ask for a new text doc, and then rename it to something.udl

Double click on it, and follow the leader all the way to a perfectly constructed dsn-less connection string.

Once you are finished creating it, drag the icon onto a blank instance of notepad, and VOILA!

 

[Ovatvvon]

Thank you, didn't know about the .udl trick!

So I am correct in thinking that the dsn-less connection is faster than using ODBC?

So I'd want to select the Jet OLE DB 4.0, correct? And where is the output then? I see the ending results with all the status, but it doesn't list the string that you just provided for me. For right now I just took your string and changed the location of my DB.

Please answer the questions if you have time. Thanks for your help! -Ovatvvon :-Q

 

[Ovatvvon]

Sorry, forget the question about getting the output....didn't see the notepad statement at first. -Ovatvvon :-Q

 

[link9]

The native provider will function faster than ODBC because you have one less layer of translation happening, but consider this situation, which I ran into when we got onto our new web platform, which consists of two dedicated boxes (one web/app, and one data).

The two boxes are sitting in a cabinet on their own little subnet. Connection between the two boxes is 100mb/sec. 1.5 mb/sec on the other side of my firewall which leads to the DNS servers about 10 cabinets down (actually, I lose a little of that speed through the firewall, but... close enough).

In this case, a DSN offers me just as many ups as it does downs. Yes, a registry lookup happens each and every time I do a connection to the database, but it also offers me great portability. If I used a DSN-less connection w/ the data server's IP address, then each time I wanted to connect, a request would go out to the DNS server to resolve the address. That happens over the 1.5 mb/sec connection. And then, the data would be returned to my web/app box over that same connection, whereas if I ask for it on the subnet that they are on together, all communication happens at 100mb/sec.

Now I might consider using the IP for the subnet, and then I'd get the same speed using a DSN-less connection, but then I would lose the portability that allows me to develop on my internal development servers (using the same DSN name), and then just stick the exact same scripts on the server, with no modification whatsoever.

Now an argument could be made that I could re-do my internal IP addresses to match the ones in the office to the ones at the datacenter, but add to that the fact that using a DSN is quicker and easier than the DSN-less connection, and I'm going to stick with DSNs. I just like them better.

 

[shaddow]

Link9-> if i Use
"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("blahblah&quot
will this use ODBC drivers?
Cuz wend i use the "Microsoft.Jet.OLEDB.4.0" part on an NT machine cruched cuz it have Microsoft.Jet.OLEDB.3.5
but wend i use DRIVER={Microsoft Access Driver (*.mdb) all worked OK. ________
George, M

 

[Ovatvvon]

I'm having the opposite problem. When I'm using DRIVER={Microsoft Access Driver (*.mdb) (which uses odbc) I have problems (which I used to not have while using it) and it has problems accessing the "registery key" or somthing to that effect...or in other instances I get:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
/ie/signup/signup2.asp, line 195

which I don't understand because I'm setting the same parameters as always. rs.Open sql, conn, 0, 1...Even tried entering full line: rs.Open sql, conn, 0, 1, adCmdText

Confused! Never had this problem before... -Ovatvvon :-Q

 

[pgebs]

I have a similar problem. My ISP runs a separate webserver for SSL and non-secure webspace. I want to share an Access 2000 databse between them. This seems to result in the error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x4fc Thread 0x92c DBC 0x1077b024 Jet'.

This is using:
sConnString = "DRIVER={Microsoft Access Driver (*.mdb)};PWD=blahblahblah; DBQ=//[ip address]/[file path]...

Using:
sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=//[ip address]/[file path];PWD=blahblahblah;Persist Security Info=false"

I get the error:
Microsoft JET Database Engine error '80004005'
Could not find installable ISAM.

Any help on this would be greatly appreciated.

Thanks
Paul

 

[link9]

Shaddow, yes:
"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("blahblah&quot
is ODBC -- more or less the same performance that you will get from a DSN.

Ovatvvon, that error always means just what it says, that you are trying to call something with not enough parameters in your call... although that does seem strange since your open statement looks fine to me. :-0 There has to be something else going on there.

pgebs, this error:
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x4fc Thread 0x92c DBC 0x1077b024 Jet'.
Means that your database has been left open by some other process, be it an actual user or maybe you forgot to close your connection and set it = nothing somewhere. That's the only time I've ever seen that error.

That second error makes it seem like you don't have the correct OLEDB provider installed on your server. I would suggest getting the latest service pack(s) for your server, specifically the MDAC (Microsoft Data Access Components)


paul

 

[Ovatvvon]

Execellent. I will try that Paul. Thanks! -Ovatvvon :-Q

 

[Ovatvvon]

I mean, I'll try installing service packs...even though that comment wasn't intended for me, might fix this or another problem I'm having. -Ovatvvon :-Q