Ask a new question

Compile error in Access 2013 query expression

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?

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

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

-Tom.
Microsoft Access MVP
Phoenix, AZ

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Is the database in a Trusted Location? A2013 won't run VBA code otherwise.
John W. Vinson/MVP

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

yes, the application is in a trusted location

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

I don't think this will help. I believe the move from 32 bit to 64 bit Access is part of the problem.

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

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.

14 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

how i can do that

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Create a shortcut like I indicated, then execute it.
-Tom.
Microsoft Access MVP
Phoenix, AZ

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 4, 2024 Views 5,126 Applies to: