Urgent: SQL Append Query Syntax

[rushforth]

Hi,

I have an append query and I want to update a table in my current database with a table from an external database (Reporter_Update). The external databases' directory path is stored in a text box (Text109) on one of the current databases' forms (MainFormFilter).

How do you reference a form in SQL? (the bit in green below)
How do you concatenate two strings together in SQL (red below)
How do you write a string in SQL? (blue below)

------

INSERT INTO Dwelling


IN Forms!MainFormFilters!Text109 &" "& "\Reporter_Update.accdb"
SELECT Dwelling.*
FROM Dwelling;

------

Hopefully you can clearly see my confusion. I'm not use to SQL and get muddled between it and VBA.

Thanks,

James

[Rixxe]

http://www.1keydata.com/sql/sql-concatenate.html
http://www.oooforum.org/forum/viewtopic.phtml?t=73906

Google is generally useful for this kind of thing, mainly because its fairly easy to obtain with 1 minute of searching....

The links above should help, If you get stuck come back, im sure someone will be able to help you out. (But have a go first! Typing in 'SQL concatenate' into google isnt hard)

Good Luck

[rushforth]

I always try Google first but I can’t seem to get the syntax right. For example if I use the concatenate method mentioned on your link (in red) it still brings up an error:

INSERT INTO Dwelling
IN Forms!MainFormFilters!Text109+' '+"\Reporter_Update.accdb"
SELECT Dwelling.*
FROM Dwelling;

Thanks anyway but I really need someone to complete the code above for me. My SQL knowledge is non-existent unfortunately.

James

[orange]

There are free video tutorials here that may help
http://www.datapigtechnologies.com/AccessMain.htm

And several examples and tutorials here
ttp://www.fontstuff.com/siteindex.htm#access

Both sites are great references.

[rushforth]

Still no joy. Can know one help? I thought it would be simple.

Thanks,

James

[thhui]

It seems that you might link the external database table first to the current database.
Then you can use append query as usual manner. (A Simple thing now ???)

[rushforth]

My problem is that I want the file path of the external database to be based on a field on a form in the database. When I distribute the database the directory of the external database will change based on the form.

Thanks.

[thhui]

After you select the external database in the form,
you can code it to link the external database table.
Then, same procedure as I have said before.

[rushforth]

How do you code the link then?

I have to pretend that the external database is not accessible because I don’t want users having to go into it. Basically they all have the current application installed on their PC. There will be an 'update data' button in the current application that will look for the external 'update' database and will suck in the data from the external database tables.

I may be going down the wrong route with trying to use the append query SQL but I can’t think of another way to do it in VBA?

If I fix or declare the file path then the SQL works fine (in red) but I need the file path to be dynamic based on a =GetPath field on the main form.

INSERT INTO Dwelling
IN “C:\Users\James\Documents\Reporter\Reporter-Live\Reporter_Update.accdb"
SELECT Dwelling.*
FROM Dwelling;

Thanks

James

[Rixxe]

Not sure of the VBA code for this, so ill just describe what i think might work.

IF (file.exists(TextBox.Text) -- or a form field
(
INSERT INTO Dwelling
IN TextBox.Text
SELECT Dwelling.*
FROM Dwelling;
)
else.....

The else section can just be an error message or something of a similar nature.
If they enter where the Database is located, then you can just use the Text value of that TextBox.

Tell me if i'm warm or way off.

EDIT:

'" & Form2.namesearch.Text & "'

That seems to be the syntax for a Form Field text.



Good luck

[thhui]

How to link external database table in Access VBA?
See the following site:-
http://msaccesstips.com/2008/09/link...bles-with-vba/

[rushforth]

Hi,

Thanks Rixxe. I think I'm going to stick to trying to resolve the SQL syntax rather than starting a new quest via VBA.

When I run the following in red I get the following message 'Could not find file 'C:\Users\James\Documents\Reporter\Reporter-Live\Form1!Text109.Text'

INSERT INTO Dwelling
IN Form1.Text109.Text
SELECT Dwelling.*
FROM Dwelling;

I've also tried adding '" & Form1.Text109.Text & "' but it brings up the same message 'Could not find file', but without the path confirmation.

It’s odd because it’s bringing up the file path fine and therefore recognising the text box but then concatenates the "Form1.Text109.Text" on the end?

The Text109 contains the full path by the way = 'C:\Users\James\Documents\Reporter\Reporter-Live\Reporter_Update.accdb'

Any ideas? I think we are getting closer.
Thanks
James

[Rixxe]

Well from that, my guess is its something to do with the '&' or '''
'" & Form1.Text109.Text & "'

Honestly i'm not sure of the exact syntax, but if google doesnt give you the answer, i'd try variations of the code above and see what results you get.

Only a matter of time!

Good Luck James.



edit:

Also should add, that the names of TextBox's and fields should reflect what is being placed into them. Time and time again when i have created a program and i'm needed a specfic textbox, nothing is more frustrating than looking at textbox1 -textbox100, Rather than TextBoxFilePath - TextBoxUserAge etc.

This is also true with form names and queries etc.

[thhui]

Or other way round,
just export the external database table query to a csv file by clicking the form button.
Then import that csv file back to the current database table.

[thhui]

Recommend the following microsoft link for import data from other access database.

http://office.microsoft.com/en-us/ac...001227658.aspx