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
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
lludden
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.
ASKER
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
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
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
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
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?
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?
ASKER
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
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
ASKER
Does anyone have any inputs on this?
Thanks
Thanks
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.
ASKER
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
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
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.
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
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
ASKER
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
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
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.
Boyd Trimmell, Microsoft Access MVP
aka HiTechCoach
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works Perfect!
Thanks for sticking with me through this whole thing.
Thanks for sticking with me through this whole thing.
You're welcome. Glad I could assist.