Results 1 to 15 of 15

Urgent: SQL Append Query Syntax

  1. #1
    rushforth is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14

    Exclamation Urgent: SQL Append Query Syntax

    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

  2. #2
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    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

  3. #3
    rushforth is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14
    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

  4. #4
    orange's Avatar
    orange is offline VIP
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    6,051
    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.

  5. #5
    rushforth is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14
    Still no joy. Can know one help? I thought it would be simple.

    Thanks,

    James

  6. #6
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    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 ???)

  7. #7
    rushforth is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14
    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.

  8. #8
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    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.

  9. #9
    rushforth is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14
    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

  10. #10
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    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

  11. #11
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    How to link external database table in Access VBA?
    See the following site:-
    http://msaccesstips.com/2008/09/link...bles-with-vba/

  12. #12
    rushforth is offline Novice
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    14
    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

  13. #13
    Rixxe is offline Blessed Bear
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Location
    London (Essex ... yep!)
    Posts
    184
    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.

  14. #14
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    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.

  15. #15
    thhui is offline Competent Performer
    Windows XP Access 2002
    Join Date
    Feb 2009
    Posts
    235
    Recommend the following microsoft link for import data from other access database.

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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. append query
    By kroenc17 in forum Queries
    Replies: 8
    Last Post: 11-30-2010, 08:09 AM
  2. append query help
    By SlowPoke in forum Access
    Replies: 2
    Last Post: 09-25-2010, 09:47 AM
  3. Incomplete Syntax Clause (syntax error)
    By ajetrumpet in forum Programming
    Replies: 4
    Last Post: 09-11-2010, 09:47 AM
  4. append query
    By w_kenny in forum Access
    Replies: 2
    Last Post: 08-24-2010, 04:48 AM
  5. Append query won't append
    By yelkenli in forum Queries
    Replies: 5
    Last Post: 02-12-2010, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums