|
Home > Archive > MS Access Database with External Data > April 2006 > Defining input file location
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Defining input file location
|
|
|
| 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 2006-03-30, 11:31 am |
| 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...SUBED1=ACCESS-L
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:4C4EE3BB-1A8B-4BC3-B7EF- AC39C26CE72A@microso
ft.com...
> 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?
| |
| Klatuu 2006-03-30, 11:31 am |
| Another method is to use the UNC path rather than the drive path
\\MyServer\SomeFolde
r\SomeSubFolder\
"Roger Carlson" wrote:
> 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...SUBED1=ACCESS-L
>
>
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:4C4EE3BB-1A8B-4BC3-B7EF- AC39C26CE72A@microso
ft.com...
> the
>
>
>
| |
|
| 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, ""
"Roger Carlson" wrote:
> 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...SUBED1=ACCESS-L
>
>
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:4C4EE3BB-1A8B-4BC3-B7EF- AC39C26CE72A@microso
ft.com...
> the
>
>
>
| |
|
| 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.
"Klatuu" wrote:
[color=darkred]
> Another method is to use the UNC path rather than the drive path
> \\MyServer\SomeFolde
r\SomeSubFolder\
>
> "Roger Carlson" wrote:
>
| |
| Klatuu 2006-03-30, 1:32 pm |
| 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" wrote:
[color=darkred]
> 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.
>
> "Klatuu" wrote:
>
| |
| Roger Carlson 2006-03-30, 1:32 pm |
| 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...SUBED1=ACCESS-L
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:F181B6A5-050D-427A-9329- 11CE6BD371BB@microso
ft.com...[color=darkred]
> 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, ""
>
> "Roger Carlson" wrote:
>
current[color=darkre
d]
save[color=darkred]
macro[color=darkred]
statement.[color=darkred]
of[color=darkred]
or[color=darkred]
| |
|
| 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" wrote:
> 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...SUBED1=ACCESS-L
>
>
>
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:F181B6A5-050D-427A-9329- 11CE6BD371BB@microso
ft.com...
> current
> save
> macro
> statement.
> of
> or
>
>
>
| |
| Roger Carlson 2006-04-05, 11:37 am |
| 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...SUBED1=ACCESS-L
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:9F8A8C47-036B-4464-962A- 206B361BD6A7@microso
ft.com...
> I have never done this converson to a module before. I can't test you
code[color=darkred]
> below because I can't find a way to run the module. What am I missing?
>
> "Roger Carlson" wrote:
>
State.CSV",[color=darkred]
State.CSV",[color=darkred]
code.[color=darkred]
Then[color=darkred]
choose[color=darkred
]
a[color=darkred]
location[color=darkr
ed]
location[color=darkr
ed]
| |
|
| 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" wrote:
> 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...SUBED1=ACCESS-L
>
>
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:9F8A8C47-036B-4464-962A- 206B361BD6A7@microso
ft.com...
> code
> State.CSV",
> State.CSV",
> code.
> Then
> choose
> a
> location
> location
>
>
>
| |
| Roger Carlson 2006-04-06, 8:29 pm |
| 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_Clic
k:
Exit Sub
Err_cmdRunCode_Click
:
MsgBox Err.Description
Resume Exit_cmdRunCode_Clic
k
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...SUBED1=ACCESS-L
"Bob" <Bob@discussions.microsoft.com> wrote in message
news:4E4908A0-2D23-4570-9AE9- 351D1824AA64@microso
ft.com...
> 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[color=darkred]
> tells you how to call a module.
>
> "Roger Carlson" wrote:
>
on[color=darkred]
the[color=darkred]
missing?[color=darkred]
Data",[color=darkred]
Data",[color=darkred]
the[color=darkred]
VB[color=darkred]
then[color=darkred]
the[color=darkred]
creating[color=darkr
ed]
path[color=darkred]
|
|
|
|
|