Compiler error on a non-existent line

SydneyGeek

MrExcel MVP

Joined

Aug 5, 2003

Messages

12,251

• Jun 14, 2011

• #2

Have you searched the queries?
Also, look in the References to see if any are marked as MISSING. If you find one, deselect it then browse down the list until you find the latest available match, and select that. OK to get out and you should be up and running.

Denis

 

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Jun 15, 2011

• #3

Thanks SydneyGeek. I have opened VBA, then did a Ctrl F to search for the relevant line given in the error message, without success. There are no missing References. All the References on the two computers are identical, as is the Access 2010 and VBA 7.

The only difference is that one is Win 7 Ultimate ( Access database works OK there), and Win 7 Professional ( errors there). Both are 64 bit.

Can you please advise how I search the Queries?

 

Last edited: Jun 15, 2011

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Jun 15, 2011

• #4

The other interesting error is that when I run the Excel workbook on my work computer to transfer data, it opens the Access database OK, but can't find the relevant Sub to transfer the data.

This doesn't happen on the home computer where the software was developed. The Sub is entered, data is transferred immediately to the Excel workbook, and then the sub closes.

I would welcome any and all suggestions on how to find the problem

 

Upvote 0

D

Derek Brown

Well-known Member

Joined

Dec 26, 2005

Messages

2,390

• Jun 15, 2011

• #5

This may not be the cause of your particular problem but if there is an add-in or third-party software involved you may have problem if you installed the 64-bit version of Office.

Microsoft recommends the 32-bit version of Office on 64-bit machines for most people:

http://office.microsoft.com/en-us/w...-version-of-microsoft-office-HA010369476.aspx

 

Upvote 0

SydneyGeek

MrExcel MVP

Joined

Aug 5, 2003

Messages

12,251

• Jun 15, 2011

• #6

I've used this to search queries:

Note -- Set a reference to DAO before running this code (Microsoft DAO 3.6 Object Library)

Code:

Function FindContentInQueries()
    Const STR_FIND = "NZ"
    Dim qdf As DAO.QueryDef
 
    For Each qdf In CurrentDb.QueryDefs
        If InStr(1, qdf.SQL, STR_FIND) > 0 Then
            Debug.Print qdf.Name & "; " & qdf.SQL
        End If
    Next qdf
End Function

Change the constant to the string you are trying to find.
Run the code, then check your Immediate window (Ctrl+G to display) for any query names and their corresponding SQL.

Denis

 

Last edited: Jun 15, 2011

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Jun 16, 2011

• #7

Thanks for the information from MS, Derek. That could be part of the problem. How do I tell if it is Offcie 64 bit or 32 bit? Both the C:\Program Files and C:\Program Files(x86) both contain MS Office Folders on the work computer.

Many thanks for the code to search the queries, Denis, much appreciated

 

Upvote 0

D

Derek Brown

Well-known Member

Joined

Dec 26, 2005

Messages

2,390

• Jun 16, 2011

• #8

To find out the version etc. in Access, on the 'File' tab select 'Help' and you will see the 'About Access' details on the right.

 

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Jun 16, 2011

• #9

Thanks Derek.

mmmmmm..... the problematic work computer uses 64 bit office, whereas my home computer uses 32 bit office - that is the computer I used to develop the software.

I am confident that is the problem, thanks to your advice and links, Derek.

 

Upvote 0

boblarson

MrExcel MVP

Joined

Nov 14, 2008

Messages

1,964

• Jun 16, 2011

• #10

While this problem may have to do with 64bit vs 32bit, I just wanted to point out that if you download the free V-Tools, you can use its TOTAL DEEP SEARCH tool, which not only searches code but also tables, queries, forms, reports, (not sure about macros).

And for about US $40, you can get Rick Fisher's Find & Replace which is an awesomely good tool for searching items as it gets into properties and options and all of that too. I use it at work and would love to buy a copy for home as soon as I can afford the extra.

 

Upvote 0