1

I am maintaining some legacy classic ASP code through a hardware move. The code queries a local Access database. On the old system, the database connection was opened as follows:

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='path to mdb file';"

Rather than cajole the old Jet driver onto the new system, I hoped to simply update the connection string to:

"DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=path to file;"

This new connection string breaks any queries that have string values surrounded by double-quotes. For example, this query works with the old Microsoft.Jet.OLEDB.4.0 driver but errors with the new one:

conn.Execute("UPDATE table SET field1=1 WHERE field2=""test"";")

Replacing double-quotes with single-quotes fixes individual queries, i.e. this works with both drivers, but would be a big refactoring job and likely to introduce bugs into trusted code:

conn.Execute("UPDATE table SET field1=1 WHERE field2='test';")

I am seeking a way to have the Microsoft Access Driver (*.mdb) use the existing queries (that have strings surrounded by double quotes) without refactoring them. Thanks in advance.

Mark F.
  • 21
  • 5
  • 1
    If you use `ADODB.Command` object with the `CommandType = adCmdText` you can build [parameterised dynamic queries](https://stackoverflow.com/a/21629707/692942) that don't require hardcoded quotes. That's the beauty of parameterised queries, you define what the parameter data type is and the provider does the rest. – user692942 Mar 02 '19 at 11:29
  • A [useful reference](http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx) for mapping ADO data types in Microsoft Access. – user692942 Mar 02 '19 at 13:35
  • 2
    Are you sure the JET OLEDB driver isn't installed - it's been there by default on every version of Windows Server I've used to date? You need to enable 32 bit in the app pool – John Mar 02 '19 at 14:35
  • @john didn't the it become ACE after Microsoft Access 2003? – user692942 Mar 03 '19 at 10:05
  • Related: [Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb](https://stackoverflow.com/a/14401857/692942) – user692942 Mar 03 '19 at 10:13
  • @Lankymart AFAIK, ACE replaced JET as the default type of database created by Access after v2003, however saving in JET (or .mdb) format rather than ACE (.accdb) remains an option to this day. My understanding is also that the JET OLEDB driver still ships with Windows, but that the ACE driver has to be installed. – John Mar 06 '19 at 19:07
  • 1
    Just to add that I have successfully connected to a JET database using a `Microsoft.Jet.OLEDB.4.0` connection string on a pretty much out of the box installation of Windows Server 2016 hosted by Amazon Web services. As you may have gathered from previous comments, it's incorrect to talk of an "Access database" although I'm guilty of this myself. Access is a piece of software for reading and editing two types of flat file database - JET and ACE – John Mar 07 '19 at 11:40

0 Answers0