Access Issues

I have a new customer who has an old MS Access database / application running on his computer. He uses it daily to enter Workorders & Customer info.

He had a MS Access person who managed & maintained it for years (some of the data goes back to 1985!!!). It is currently running (on his machine) under Access 2003. The MS Access person retired and is not available.

I have sort of inherited this. I suggested to him that I get a copy of the database so I could install it on my machine & play with it; that way, I would not risk doing anything to his daily work version while I was trying to learn it.

My own background includes a LOT of Access experience, but it is very old, I did an online Access database maybe 5 years ago, other programming, form design VBScripting, Macros, etc., a long time ago (10 - 15) years.

I have gotomypc access to his computer, so I can look at anything there, copy things, etc.

When I try to run it here, I get the attached. This MIGHT be because I have an expired version of Access 2010. I see to recall that the AutoExec macro (if any) is what is done at application startup. My guess is it accesses files / folders on his computer.

How can I look at what is in AutoExec on his computer in Access 2003?

Thanks.
macro-error.jpg
Richard KortsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lluddenCommented:
First, get a current version of access.  If it is expired, it won't let you do anything.  You can probably get by with the 2013 version to at least look at the macros and stuff.  Remember to hold down the shift key when opening the file to prevent the autoexec macro from running.
0
Richard KortsAuthor Commented:
To lludden

Yes, I got Access 2013. I can open everything. Thanks.

The AutoExec macros executes the attached function. I don't fully understand it (I get parts). Maybe I have to have the same dir structure underneath the folder sprinklers on the customer machine?

Sprinklers.txt is also attached.
SetFileLocations.txt
sprinklers.txt
0
lluddenCommented:
You will need the  c:\Sprinklers folder.
for the  Images=K:\As Builts\
you can put those in a folder somewhere on your C: drive and use the SUBST command to create a drive letter to point to it:

MD C:\SprinkerImages
SUBST K: C:\SprinklerImages
then copy the entire K:\As Builts\ folder to the new K drive on your computer
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You will need the file Sprinklers.txt on the root directory of C:

Edit c:\Sprinklers.txt  to have to correct locations  


And have you verified:

1) the VBA code will compile without errors
2) there are no VBA references marked as MISSING


Also, what method are you using to enable/trust the VBA code?

Once the above has been addressed, try running the SetFileLocations() from the immediate window (Ctrl-g).  What line generates a  error?
0
Richard KortsAuthor Commented:
The folder Sprinklers.txt exists at the root of C: It's attached.

The pseudo folder K is there with directory As Builts with all the images in there.

I don't know what the immediate window is. When I open the database (without holding down shift), I get the attached error. 3 files attached (plus sprinklers.txt); the initial error, the VBA code & the yellow highlighted line the debugger indicated.

Any ideas?
autoexecerror.jpg
sprinklers.txt
vba-error.jpg
vba-code.txt
0
Richard KortsAuthor Commented:
Does anyone have any inputs on this?

Thanks
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
The folder Sprinklers.txt exists at the root of C: It's attached.
Folder? I think are saying that you have the file c:\Sprinklers.txt

The pseudo folder K is there with directory As Builts with all the images in there.

K needs to be a mapped drive letter or you can edit the file c:\Sprinklers.txt and change the location to from Images=K:\As Builts\ to something like Images=c:\Sprinklers\As Builts\

About the code you posted:

When I tried to test the code you posted it go error for variables not defined. Once I DIM'ed the missing variables the code compiled. I had to created the two required tables it worked. The tables I created were CompanyInformation in the back end and AttachedTables in the front end.


Did you copy both the front end mdb and the back end mdb (LawnData.MDB)?

Did you keep the  databases in the .mdb format?

The code does not appear to be an issue. I think it is a set up issue. Getting all the databases .mdb files into the correct locations to match the VBA code  I recommend that you put the front end and back end in the folder c:\Sprinklers on your PC.


Note:
You can also use the built-in Link Table Manger to relink the tables to the new location on you PC. This should get the app working to buy you some time to learn the app and get up to speed with Access.
0
Richard KortsAuthor Commented:
To HiTechCoach,

Thanks for all your help.

(1) Both Lawndata.MDB & Sprinklers.MDB are in C:\Sprinklers.

(2) Sprinklers.txt is in the root of C:

(3) The folder SprinklerImages is "mapped" to K, using SUBST from the DOS command line. For whatever reason, that association disappears when I reboot this computer.

(4) Access VBA gives a runtime error 3265 (on startup). If I click debug, I get the attached highlighted in yellow. I don't know what that means.

(5) I do not know how to compile the VBA. Can you tell me how to do that?

(6) The customer has Access 2003 on his computer; my gut feel is there are differences, I have (& JUST got) Access 2013.
debug.jpg
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Can you open the table AttachedTables from the navigation pane? Is it a linked table? What are the field names in the table so I can recreate it.  This information will help me test the code.

FYI: I have been running the code in Access 2003. It also worked in Access 2010.


About the version of Access:
I have seen issues with using Access 2007/2010/2013 to edit an MDB that make it no longer compatible with Access 2000/2002/2003.

I have been a full time developer creating  custom and commercial Access apps for 15+ years.  I find it critical to use the same version of Access that the client has. Event the same Windows version can be important.  That is why I have every version of Access available. And ever version of Windows back to XP in both 32 and 64 bit. This allows me to duplicate all my client's environment.


(3) The folder SprinklerImages is "mapped" to K, using SUBST from the DOS command line. For whatever reason, that association disappears when I reboot this computer.
That is normal.

 If you use Windows Explorer to create a mapped drive letter you can check the option to "Reconnect at Logon".

Boyd Trimmell, Microsoft Access MVP
aka HiTechCoach
0
Richard KortsAuthor Commented:
To HiTechCoach

Thanks for all that.

See attached jpgs. Is this what you need?

Is there a way I can obtain Access 2003? Ebay?

Thanks
attachedtables.jpg
attachedtablesdata.jpg
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Thanks for the screenshots. I had created the AttachedTables exactly like that.

I will bundle up the code I created and attach it for you to try.

Is there a way I can obtain Access 2003? Ebay?
I get all the versions through my MSDN subscription.

(5) I do not know how to compile the VBA. Can you tell me how to do that?
Open the VBA Editor: Debug > Compile


Boyd Trimmell, Microsoft Access MVP
aka HiTechCoach
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
I was able to duplicate the Item Not Found in Collection error if I deleted the linked tables before running the code.

Make sure all the tables are linked that are listed in the AttachedTables. You can use the Linked Table Manager to check. If any are missing you will need to recreate the linked tbale(s) manually.

I have attached the code I have been testing. I did make a minor modification so that all the files can be in the the same folder as the as the front end (sprinkler.mdb). Unzip the attached to any folder and open sprinkler.mdb.
sprinklers131016b.zip
0
Richard KortsAuthor Commented:
TheHiTechCoach

I'm sorry this is so complex.

See attached. I'm completely confused.

The table called AttachedTables contains 6 rows; as best I can tell there are 5 attached tables.

There is no CompanyInformation. It is in LawnData.mdb. How do I attach it?

Also, whenever I open the database holding down the shift key, I get the attached (Access.jpg). EVEYTHING is greyed out? How do I do ANYTHING???

I accidentally stumbled onto it before; I can't remember how.

Interface is INCREDIBLY confusing.
linked-tables.jpg
Access.jpg
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
Let's tackle one issue at a time. As you fixed one issue it may also fix many other issue  you are having.  Firest let's make sure all the tables are working from the original posted issue.

There is no CompanyInformation. It is in LawnData.mdb. How do I attach it?

To create a new linked table click on the External data tab. In the Import and link group click on Access.

You will get a Wizard. Select the back end database using the browser button. Be sure to select the lower option to "Link to the data source..."

FYI:
Also, whenever I open the database holding down the shift key, I get the attached (Access.jpg). EVEYTHING is greyed out? How do I do ANYTHING???
Press F11 to open the navigation pane.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard KortsAuthor Commented:
Works Perfect!

Thanks for sticking with me through this whole thing.
0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
You're welcome.  Glad I could assist.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.