ODBC vs, Direct in Access

why would one fail?

[txbakers]

I went and switched my connection strings to direct connect using the JET engine for Access. It seemed to work fine.

Today I noticed that certain pages wouldn't pull, returning "unspecified" errors at the Open command. When I changed back to ODBC with Data Source names, it worked fine. Are there any syntax differences in the SQL statement? Shouldn't be.

I read somewhere about an advantage of ODBC with connection pooling. Does this have any bearing on the issue?

Also, most discussions of mySql involving ASP seem to want to use ODBC rather than direct server connections. Any idea about that?

[korkus2000]

What version of access are you using. If you are using 2000 and not using jet 4 you will get some weird errors. Using a JET provider should be faster for access than dns. The only difference should be the connection string. Can you post the connection string on the pages that are giving you trouble and the versions of mdac, jet, and access on the server and your development computer.

[edited by: korkus2000 at 6:53 pm (utc) on Sep. 9, 2002]

[txbakers]

Access 2000 with Jet 4.0

[PaulPaul]

I would probably reccomend ODBC.

Did you see a performance increase when switching to the native Jet driver?

Also, if you are using recordsets. There are ways to speed up the access to the DB depending on recordset syntax [ rs1!field1 vs rs1.fields("field1") vs rs1.fields(0) ]

Do some tests, and search the net for discussion of this.

[txbakers]

That's very interesting Paul! I didn't notice much of an improvement over the ODBC.

I'm using javascript for the ASP language with recordsets. I'll have to investigate if there is any more information about different syntax.