"Function not available..."

[maw230]

I am receiving this error on two of the office computers while the others aren't affected. A colleague helped me to register two .dll's on the 'offending' PC's hoping that would solve the issue, but it did not...

Can someone point me in the right direction?

 

[boblarson]

What is the entire SQL string? Are all of the computers on the same operating system with the same service packs for Windows and for Office?

 

[maw230]

Same operating systems yes. I would assume the service packs are the same, but assuming never gets me anywhere, so I'll check. Here is the SQL:

INSERT INTO tblCStores ( STORE_NUMBER, CLOSED_DATE, OPENED_DATE, ACQUIRED_DATE, COUNTY, REFERENCE_NAME, DISTRICT, ACQUISITION_DATE, STORE_ABBREVIATION, STORE_TYPE, DIVISION, REGION, CITY, STATE, ZIP )
SELECT GPDATA_STORE.STORE_NUMBER, GPDATA_STORE.CLOSED_DATE, GPDATA_STORE.OPENED_DATE, GPDATA_STORE.ACQUIRED_DATE, GPDATA_STORE.COUNTY, GPDATA_STORE.REFERENCE_NAME, GPDATA_STORE.DISTRICT, GPDATA_STORE.ACQUISITION_DATE, GPDATA_STORE.STORE_ABBREVIATION, GPDATA_STORE.STORE_TYPE, GPDATA_STORE.DIVISION, GPDATA_STORE.REGION, GPDATA_STORE.CITY, GPDATA_STORE.STATE, GPDATA_STORE.ZIP
FROM GPDATA_STORE
GROUP BY GPDATA_STORE.STORE_NUMBER, GPDATA_STORE.CLOSED_DATE, GPDATA_STORE.OPENED_DATE, GPDATA_STORE.ACQUIRED_DATE, GPDATA_STORE.COUNTY, GPDATA_STORE.REFERENCE_NAME, GPDATA_STORE.DISTRICT, GPDATA_STORE.ACQUISITION_DATE, GPDATA_STORE.STORE_ABBREVIATION, GPDATA_STORE.STORE_TYPE, GPDATA_STORE.DIVISION, GPDATA_STORE.REGION, GPDATA_STORE.CITY, GPDATA_STORE.STATE, GPDATA_STORE.ZIP, GPDATA_STORE.COMPUTER_CODE, GPDATA_STORE.PLAN_O_GRAM
HAVING (((GPDATA_STORE.STORE_NUMBER)>30 And (GPDATA_STORE.STORE_NUMBER)<>3823) AND ((GPDATA_STORE.CLOSED_DATE)<#1/1/1902#) AND ((GPDATA_STORE.OPENED_DATE)<=DateAdd("yyyy",-1,DateSerial(Year(Date()-1),1,1))) AND ((GPDATA_STORE.ACQUIRED_DATE)<=DateAdd("yyyy",-1,DateSerial(Year(Date()-1),1,1))) AND ((GPDATA_STORE.COMPUTER_CODE)="Y") AND ((GPDATA_STORE.PLAN_O_GRAM)<>"N"))
ORDER BY GPDATA_STORE.OPENED_DATE DESC;

Edit:
Both PC's are running Office SP2. One of them matches mine exactly, and the other appears to be older. So, if one is an exact match the error probably lies elsewhere?

 

[boblarson]

Both PC's are running Office SP2.

Office WHAT SP2 (Office 97, 2000, 2002, 2003, 2007, 2010???)

 

[maw230]

Office 2007

 

[boblarson]

I'm not seeing anything out of the ordinary with the functions, but perhaps the computers that are having the problem are having reference issues.

1. Open the database frontend on a computer which has this problem.

2. Go to the VBA Window and to TOOLS > REFERENCES.

3. Check to see if any are marked as MISSING.

4. If there are, then uncheck the check box beside those and then close the dialog and reopen the database file.

5. If there aren't any marked MISSING, then try unchecking the DAO reference that is checked, close the dialog and then reopen the references dialog and check it again. If you are on Access 2007 or 2010 you will need to look for the reference marked Microsoft Office Access 12.0 Database Engine Object Library (or 14.0 if 2010) and that is the DAO reference where on older versions it actually said Microsoft DAO 3.x Object Library (where .x is either .51 or .6).

Also, I noticed that you have at least one field (STATE) which might need to have brackets around it in each part of the query. It probably doesn't have anything to do with this, but just in case, it might be good to use brackets as STATE does appear to be a T-SQL reserved word.

 

[maw230]

Thanks for all the great info, bob!
Will post back after I have some time to trouble shoot.

 

[maw230]

In the VBA window under Tools, the 'References' button is grayed out - unclickable. Is this to do with an option I have set in the front end database?

Also, when trying to view a module I get a message box that says "Project is not viewable"

 

[boblarson]

Ah, they must have an ACCDE or MDE instead of an ACCDB or MDB file. Can you try out a copy of an ACCDB/MDB (whichever is the one you currently have as the master) and see if they still get that error? Or are they running the RUNTIME instead of a full version of Access (that would also explain the grayed out option and inability to get to modules).

 

[maw230]

Yeah, it is an ACCDE.

I created a brand new accdb and was able to access the References just fine. There was no DAO reference checked. I found DAO 3.6 and checked it, but I got a new error:
"Name conflicts with existing module, project, or object library"
This is on a new blank database.

I remember this error while working on the other PC a while back, and it was never resolved.

 

[boblarson]

I created a brand new accdb and was able to access the References just fine. There was no DAO reference checked. I found DAO 3.6 and checked it, but I got a new error:
"Name conflicts with existing module, project, or object library"

I told you that for the Access 2007/2010 formats (ACCDB) you need to look for MICROSOFT OFFICE 12.0 ACCESS DATABASE ENGINE OBJECT LIBRARY and NOT DAO 3.6. That Access Database Engine library IS DAO in the newer file formats.

 

[maw230]

Right, "MICROSOFT OFFICE 12.0 ACCESS DATABASE ENGINE OBJECT LIBRARY"
is there.

 

[boblarson]

Right, "MICROSOFT OFFICE 12.0 ACCESS DATABASE ENGINE OBJECT LIBRARY"
is there.

So did you try unchecking it, closing the dialog and then reopening the dialog and rechecking it, closing the database and then reopening and seeing if the problem still exists?

 

[maw230]

Yes, that did not work either. I attached a screenshot of the offending pc's references.

 

[SmallTime]

Shot in the dark - I wonder if there's a bit of corruption creeping in there. I would try

1. copying a fresh (known good) front-end to the offending PC (I assume all PC's have their own front-ends)

On the flip side you could copy the front end from the offending PC to a known good PC and see if it works there


if that don't work then

2 Uninstall Access, Reboot, Reinstall Access.

 

[maw230]

1. copying a fresh (known good) front-end to the offending PC (I assume all PC's have their own front-ends)

On the flip side you could copy the front end from the offending PC to a known good PC and see if it works there

Tried both of these, and same results. I wonder if a reinstall is what's needed..

 

[SmallTime]

What happened when you copied the front end from the offending pc to a good pc. Did it still produce the same error?

 

[maw230]

No, it worked fine.

 

[SmallTime]

No corruption in the front end then.

If all else is equal, same references, no missing references, same access version, same patch, same user privileges, same drive mappings, then I think a reinstall might be worth trying.

 

[gemma-the-husky]

mdes/accdes are tricky

if the references are incorrect, you cant fix them in an mde.

I would try putting the database on one of the problem machines, and recompiling it in ON that machine. See if that helps.