Defining an Append Query IN path from a form

[TR6]

I am trying to set up an append query to append to an external Access database table where the path to the external database is user defined from a form. I have set my query IN clause to '[Forms]![FormName]![TextBox].mdb'. Where TextBox is user input of [C:\My Documents\DataBase].

I have two proplems when I run the query:

When I run the query, I get an error message that it cannot find file "C:\My Documents\[Forms]![FormName]![TextBox].mdb". Access has somehow combined and truncated the user defined path and the [Form] and [FormName].

The other problem is if I clear the [TextBox] so that the Text Box is blank, and run the query, I get exactly the same error message.

I've run the query when I hard code the path into the IN clause and it works fine.

Any suggestions?

 

[KornGeek]

It appears that Access may not support doing it this way.

Have you considered linking a table in your current database to the appropriate table in this other database? It can be relinked at run-time (using the path from the form), and then you can append to the table in the current database without a problem.

 

[rhammud]

instead of writing "IN [Forms]![FormName]![TextBox].mdb", try the following and i think it will work:


"IN " & [Forms]![FormName]![TextBox] & ".MDB"

be careful about the spaces in "IN " and NO spaces in ".MDB"

good luck
Rania

 

[TR6]

Thanks for your responses.

rhammud - I tried that Access rewrote it to: 'Forms!FormName!Textbox&'[&.mdb&]and I still got the same error message.

KornGeek - I've never done any linking - I'm a beginning novice. But, don't I still have the problem of getting Access to accept a variable for the path name?

Is there any advantage to trying to get this to work through VB and TransferDatabase? I've been in the HELP section.

 

[rhammud]

can u post the whole statement? maybe i can spot the error.
is it the same error message after u tried my solution?

 

[KornGeek]

faq700-1584 covers how to relink tables at runtime. It is a lot of code, but if you take the time to understand it, it works great.

Fortunately, you can modify the code (once you understand it) to accept a variable as the path to the table.

If you aren't comfortable with code, this will not be easy, but I believe it will work for what you want to do.

 

[TR6]

rhammud:

Here is the general SQL statement with detail around the path name. I just noticed that Acess inserted [&.MDB&] as part of my INSERT INTO [TableName]. Anyway, here it is:

INSERT INTO [&.MDB&].[TableName] ([field1],[field2]...[fieldn])
IN '&Forms!FormName!TextBox&'[&.MDB&]
SELECT [field1], [field2]...[field8]
From [TableName]

KornGeek - thanks for the FAQ. I'll print out the code. I've gone through some code. I find it easier to read and understand than to create and write.

 

[rhammud]

where do u have this statement? in VBA module? it cant be.
and what do u mean by [&.MDB&] is inserted by Access

 

[TR6]

Rhammud,
The statement is not part of a VBA Module. It is the SQL statement of the Append Query.