Defining input file location

[Bob]

In the help information for Access 2K it states that for creating a macro
definition to import data you need to include the complete path statement.

My problem is different users are mapped differently to the location of the
datbase.

Is there something I can do to specify to look in the current location or
better yet, one folder down the directory?

 

[Roger Carlson]

Sure. CurrentProject.Path will return the path and filename of the current
database. But you can't use it in a macro. You'll have to use VB code.
Easiest way to do that is create a macro with a hard-coded path. Then save
it as a VB module (right-click the macro, choose Save As..., then choose
Module in the As box). Once you have your VB function, replace the
hard-coded path with CurrentProject.Path.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Another method is to use the UNC path rather than the drive path
\\MyServer\SomeFolder\SomeSubFolder\

 

[Bob]

I get a compile error. Below is the statement

DoCmd.TransferText acImportDelim, "Cognos Data Import", "Cognos Data",
CurrentProject.Path "FOCUS and Rsktek Pending by LOB Adj and State.CSV",
False, ""

 

[Bob]

Klatuu, my problem is not all users are mapping this as the same drive or
directory. Some start at a higher directory level and some map directly to
the folder the DB is in.

 

[Roger Carlson]

Try:
DoCmd.TransferText acImportDelim, "Cognos Data Import", "Cognos Data",
CurrentProject.Path & "\FOCUS and Rsktek Pending by LOB Adj and State.CSV",
False, ""

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Klatuu]

Then I suggest you use the code at this site:
http://www.mvps.org/access/api/api0001.htm
To provide a way to navigate to the location.
The other solution is to have each user create a new mapping that starts
from a consistent directory. The drive letters do not have to be the same if
you use UNC paths, but the directory structure within the UNC must (as you
have found). For performance, I would suggest you set the mapping directly
to the folder where the files will be.

 

[Bob]

I have never done this converson to a module before. I can't test you code
below because I can't find a way to run the module. What am I missing?

 

[Roger Carlson]

To test-run code in a module, just put your cursor anywhere in the code
(say, on the DoCmd line) and click the Run button (has a blue triangle on
it). To run the code in a production environment depends on your
application and when and how you want to run it. Usually, it is in some
other code segment (say behind a command button) where you simply call the
function:

Call MyFunction()

Where "MyFunction" is the actual name of the function.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Bob]

Your code works great. I just can't seem to figure out how to run it from a
command button. When designing a new button there really are no options to
select the new module, and when I try and modify the existing (which was
designed to run the macro) I try and replace the DoCmd action wth the Call
Cognos Data Import Macro (after conversion it kept the same name as the
macro) and just get a compile error. Really nothing in the help screen that
tells you how to call a module.

 

[Roger Carlson]

Here's what I always do: Use the Button Wizard to create your button.
Choose Form Operations > Close form. Put some descriptive text on your
button and give it a descriptive name (like cmdRunCode). This will create a
subroutine in the OnClick event of your button something like this:

-----------
Private Sub cmdRunCode_Click()
On Error GoTo Err_cmdRunCode_Click

DoCmd.Close

Exit_cmdRunCode_Click:
Exit Sub

Err_cmdRunCode_Click:
MsgBox Err.Description
Resume Exit_cmdRunCode_Click

End Sub
-----------

Then simply replace the DoCmd.Close with your Call.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L