Access Default database folder keeps changing to Program Files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access application that imports Excel spreadsheets into tables
using Transfer Spreadsheet Macros. However, The default database folder
keeps changing and the following error keeps coming up when a transfer
spreadsheet macro executes: "The Microsoft Jet database engin could not find
the object 'C:\Program Files\Common Files\SYSTEM\MSMAPI\1033\Tbl_Leads.xls'."
However, the default database folder still shows the path for the My
Documents folder! To make it work, I have to back out a letter and replace
it again in the field so that the "Apply" button activates and click to reset
the default database folder. The transfer spreadsheet macros then work for a
short while until it errors again. How can I keep the default database
folder to stay at the My Documents folder?
 
Are you saying that you're trying to use only the file name, hoping that
Access will look for it in the My Documents folder?

There's a difference between the Default Database folder and the current,
active folder. When you use an unqualified file name, Access (and other
Windows programs) will automatically look in the current folder (as
identified by the CurDir function)

What Access will use as the current folder depends on how you opened the
database, and whether or not you run any code that changes the current
folder (such as using the File Open or File Save dialog, issuing a ChDir or
ChDrive statement and so on.)
 
I can't tell you how much I appreciate your quick response. This has been
very frustrating to figure out. Yes, in the macro I'm only indicating
filetoimport.xls in the transfer spreadsheet command, without using the full
path name. I wasn't aware of the active folder defined by the CurDir
function. I'll do some studying on that. Should I go ahead and put in the
whole path of the My Documents folder into the transfer spreadsheet command?
In other words, would it always look for the file in the My Documents folder
using the full path name?
 
Unfortunately, I don't know that it is possible to hard-code the path to
point to My Documents so that it would work for all users. Remember that the
path to YOUR My Documents folder will be different than the path to MY Your
Documents folder.

What you could do, though, is create a little function that determines the
My Documents folder for each user. You could copy all of the code between
Code Start and Code End from http://www.mvps.org/access/api/api0054.htm at
"The Access Web" into a new modules, and save that module. You could then
use fGetSpecialFolderLocation(CSIDL_PERSONAL) & "\Tbl_Leads.xls" to refer to
your file.
 
Back
Top