CODEWITHSUNDEEP
ms-accessvbaruntimems-access-2007linked-tables
RunningWithScissors
RunningWithScissors

Reputation: 71

Linked tables in Access Runtime treated differently than in Access developer version?

I have a database (dbW, we'll call it) that needs to access employee information from two tables exported from the master employee database (dbEMP) We don't want the employee's running dbW to have access to dbEMP to view all of it, thus the export of non-sensitive information (Clock#, FirstName, LastName, Foreman, HireDate, Shift) as a pair of .dbf files. "employee.dbf" and "FRM.dbf"

Until recently, HireDate was not exported. I made a third export query to export from dbEMP to create "employee2.dbf" to have the HireDate included, with the original one being called "employee.dbf". (Why? because of reasons I won't get into here)

Now, in my dbW front-end, I have a lot of code that already references "employee.dbf". So, in my bit of amazing genius (or was it?) I decided to rename these two linked tables to "employeeOLD", which still pointed to linked table "employee.dbf" and "employee" which pointed to linked table "employee2.dbf", and contains the extra field of HireDate.

I run it and test it on my workstation. It all runs fine. I didn't need to rebuild or do any find/replace stuff in the code, it just references the correct table and works.

I send out a copy of the runtime to one of the employees running it and it is broken. Runtime error 3061. Which is what? Typically it's a misspelled field name or missing field. But it worked on my PC, so what is wrong?

After doing a lot of modifications, msgbox breaks in the VBA and such to let me know where the process is in the runtime version on a PC that cannot access the code or immediate window, I finally figured it out. Even though the code says it's looking at the table named "employee", it's actually looking at linked table "employee.dbf", which is renamed in my program to "employeeOLD". I thought in order to have it look at the data in linked table "employee.dbf", I'd have to refer to "employeeOLD" in the VBA code. Turns out even though my genius move of renaming the file to what was already in the code worked on the full version, I would have needed it all reprogrammed to refer to "employee2" (which doesn't even appear on the table list on the left, unless you look at the filename of the linked tables) to make the runtime version work.

In the end, I went back to dbEMP and no longer export both versions of employee, only "employee.dbf" which now contains the HireDate field, and I've removed any linking to employee2 tables and the table name in Access is the same as the table name on the file server (minus the .dbf, of course)

So, my problem is self-solved, but I still post this and ask the question of whether or not anyone else has come across this issue, or if they can duplicate the issue.

Upvotes: 0

Views: 189

Answers (1)

RunningWithScissors
RunningWithScissors

Reputation: 71

Answer: Do not rename linked tables. The VBA code appears to still look for the original name.

If you have a better, or more thorough answer, please post.

Upvotes: 1

Related Questions