Compile error in Access 2013 query expression

Question

I have an Access 2007 database that imports a .csv file into an existing table and then transforms the imported fields and stores them in the database tables. In Access 2007, this database executes correctly using an autoexec macro.

When I attempt to run the same database using Access 2013, the execution stops at an update query that uses the Mid() function and I see the error message "Compile error, in query expression 'Mid(<string>, <start>, <length>)".

When I open the database to repair the query, none of the expressions created by Expression Builder work. When I add a column that contains the function Now(), the column is formatted as Now() instead of Expr1: Now() and I get the error message "There was an error compiling this function". Since this is using expression builder, all the compiling is done internally by Access so there is no way to recode it and recompile it.

To fix this problem when using Access 2013, I need to reprogram the Access 2007 database in a fresh database using Access 2013.

Is there an easier way to solve this problem?

Answer 1

I have discovered the reason for the error message. In the move from 32 to 64 bit Access, one of the modules in the database would no longer compile correctly. This affected how the built in functions were executed. It didn't matter that the module was not referenced in any of the database processes.

The error that caused this behavior was the change in the declare statement for Windows functions. The 32 bit statement was:

Declare Function WNetGetUser& Lib "Mpr" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName$, lpnLength&)

The 64 bit statement must be:

Private Declare PtrSafe Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" (lpName As Any, ByVal lpUserName As String, lpnLength As Long) As Long

My problem was that I couldn't find an explanation on Microsoft.com.

The answer was at http://pastebin.com/ibgW9c71

Thanks for you attention and for setting me on the right path.

Answer 2

Create a shortcut like I indicated, then execute it.

Answer 3

Is the database in a Trusted Location? A2013 won't run VBA code otherwise.

Answer 4

I'm not sure, but have you tried decompiling the application?

<path_to>msaccess.exe <path_to>your.accdb /decompile

Answer 5

yes, the application is in a trusted location