Access database functions

Question

I created a database at work on Access 2010 with queries using the left(),right() and mid() functions. I then copied the database over to my pc at home, but now these functions does not want to work on this database. My pc at home also has Access 2010. Everytime i try to run a query i get the following message, There was an error compiling this function. Visual basic contains a syntax error. Check the code and then compile it again.

I created a new DB on my pc at home to test the functions and had no problems. I also noticed that on my pc at home the heading reads Database1 : Database(Access 2007) - Microsoft Access while at work it says Database1 : Database(Access 2007 - 2010) - Microsoft Access. Both PCs have Windows 7 Professional and my home PC is about 1 year old and my pc at work about 3 months. I also updated/repaired my Access at home, but the DB still does not want to work. How can i fix this problem?

I have a simple table call it Table1 with an id column and a column named Name,

id  Name 
1  Jason
2  Casey
3  Shasha

Now if i want to use the left() function in an Access query , i get the error message: There was an error compiling this function. Visual basic contains a syntax error. Check the code and then compile it again.

Query:

Select Left([Name],2) As Short_name From Table1

Answer 1

Exact same issue with simple string functions used in a query...

Fix was not the same: I had copied in a VBA module (a function completely unrelated to using Left("FFFF",2) in a query).

The issue was that I had accidentally included "Option Compare Database" twice at the top of the copied in module.

Answer 2

I had this issue when having 64-bit office and 32-bit access...(why? is another question)

Fixed it by going to the VBA editor and hitting "compile" over and over, which took me to the offending 32-bit functions which required ptrsafe to be added to the function declarations. (Yes I dangerously added it without a second thought.) and then Access was happy.

I.e.,

Private Declare Function GetUserName _
   Lib "advapi32.dll" Alias "GetUserNameA" _
   (ByVal lpBuffer As String, nSize As Long) _
   As Long

Becomes

Private Declare PtrSafe Function GetUserName _
   Lib "advapi32.dll" Alias "GetUserNameA" _
   (ByVal lpBuffer As String, nSize As Long) _
   As Long

I can only guess that my 32-bit access's VBA is trying to compile for 64-bit because that's what office version I have installed.

Answer 3

You want while in the VBA editor go tools->references, and look for a missing reference.

You likely have a reference to some software (say word) that does not exist on the target machine.

The “step” by “step” of looking at these broken references is outlined here:

http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Broken references is perhaps one of the most common issues in Access when moving the application to another machine. It is suggested you use late binding and remove all un-necessary references to “reduce” this problem from occurring.