Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2008
    Posts
    28

    Unanswered: issue making a pass-through query an append query

    Hi guys, I am attempting to make a database of mine updatable to a data souurce using pass-through queries. Doing a select query worked great on all of them, so I added the INSERT INTO statement to the query, and I get an error. I will put the code here of one of the simple queries:

    First I used this:

    "INSERT INTO Customers ( custid, custname )
    SELECT fcustno, fcompany
    FROM slcdpm"

    and got an error saying "Invalid object 'Customers'" so I tried:

    INSERT INTO Customers ( custid, custname ) IN 'H:\db\mdb.accdb'
    SELECT fcustno, fcompany
    FROM slcdpm

    and it said invalid syntax near 'IN'


    I am relatively new to all of this, so are you not able to do this with pass-through queries? Could I make an append query within access which just called on these select queries? Any help is appreciated. Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please post your pass through SELECT. Are Customers and slcdpm in the destination database file?

  3. #3
    Join Date
    Jun 2008
    Posts
    28
    well for the query that I have above, the table slcdpm is a table from the DSN sql database which the database has read-only access too (might be the problem?) Customers would be a local table in the database.

    If i use:

    "SELECT fcustno, fcompany
    FROM slcdpm"

    It works fine, but if I use an INSERT INTO statement like:

    "INSERT INTO Customers ( custid, custname )
    SELECT fcustno, fcompany
    FROM slcdpm"

    or

    "INSERT INTO Customers ( custid, custname ) IN 'H:\db\mdb.accdb'
    SELECT fcustno, fcompany
    FROM slcdpm"

    it gives the errors I mentioned above... should I use an ms access append query that calls on the pass through query? it just seems like it would be easier if i could do it all in one query. thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    So:
    There is a SQL database containing slcdpm. You can execute a pass through query to that SQL database and SELECT data from the table?
    There is an access database (from which you are running the pass through query) that contains a table called Customers.

    You wish to insert the contents of slcdpm into Customers.

    Correct?

  5. #5
    Join Date
    Jun 2008
    Posts
    28
    right

    (apparently my reply is too short)

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The very easiest way to do this (perhaps not the best in every case but the best in most) would be to create a linked table based on slcdpm. Do you know how to do that?

  7. #7
    Join Date
    Jun 2008
    Posts
    28
    well the problem is im showing you a very simple query here, I am creating other tables in my database that are queries with up to 6 different tables joined. in addition, only one machine will have access to the data source, which will be the machine that will run these updates... however everyone in the building will be using another database that links to that database.

    does that make sense?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The problem is still the same.

    Ok - here are your options:
    1) Link the destination table. Create your SELECT query that specifies the data to be inserted. Convert this into an INSERT statement, insert into linked table.
    2) Create a HETEROGENEOUS pass through query. Pass a T-SQL statement to SQL Server using OPENROWSET() syntax to retrieve the data from the remote (as far as SQL Server is concerned) Access database and insert into the local SQL Server database.
    3) Create a HETEROGENEOUS local query, specifying connection information for the destination SQL Server table.

    I don't see how this running from one machine and not others is relevant...

  9. #9
    Join Date
    Jun 2008
    Posts
    28
    im not sure if it is relevant or not, but i guess what i was thinking was that they want me to design this in a way to where it could grab the data once, and then not have to be connected to the server, and i thought linked tables might not meet those requirements

    i apologize for my ignorance but im not quite sure i understand option 1... when you say "convert this into an INSERT statement", isnt that what I was trying but getting an error with?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by lSTellYl
    im not sure if it is relevant or not, but i guess what i was thinking was that they want me to design this in a way to where it could grab the data once, and then not have to be connected to the server, and i thought linked tables might not meet those requirements
    Let's take one thing at a time. What do you mean by this? You have data in an access database and you are trying to stick it into a SQL Server table. How do these two points tie together? What does the bit in bold mean? Which data? What do you mean by grab? What do you mean by once?

  11. #11
    Join Date
    Jun 2008
    Posts
    28
    hmm im sorry if im not making sense....

    what i mean is the access database in question is a database that will be placed on a server, and it will store just hard data. I have made a couple of other access databases that have front ends (forms) that do a variety of tasks. basically, what I want to design the main access database to be able to do, is have somebody be able to go and take a snapshot of the data from the sql server like, say, once a day, but for the most part i want this data locked in the main database and not linked in any way to the sql server (except when we are updating)

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - I get that now. Next question - why do that?

    I'm still trying to understand how this fits in with your requirement - it doesn't have any connection that I can see.

  13. #13
    Join Date
    Jun 2008
    Posts
    28
    well then maybe i am misunderstanding....

    you are saying we link the tables from the sql server to the tables in my database?

    i dont understand how we can do that if my table in my database was created from a join table query and does not exist in the database?

    something might be flying right over my head, i dont know

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - you need to look up linked tables in help.

    A linked table is a persisted link (using the DSN you mentioned earlier) to the table in SQL Server. Once you have created the link you can interact with the table in Access almost as though it were in Access.

    Try right clicking the tables screen in Access and select Link Tables. Follow the wizard (you'll be linking to an ODBC data source) and link the table. Then see if it suits.

    You do not link tables to each other (perhaps you were confusing this with relationships or joins) but creating a link between the Access database and the SQL Server table.

  15. #15
    Join Date
    Jun 2008
    Posts
    28
    ok so i linked all of the tables that i needed from the server, and i am now remaking some of the queries. i am running into some syntax issues where msaccess differs from sql i guess. this is an append query that I am running, the SQL is as follows:


    "
    INSERT INTO cv ( fjobno, subno, fpartno, fpartrev, fsono, fcompany, fddue_date, fquantity, flabact, fmatlact, fovhdact, fgroup, jfuprice, jfshipqty, fuprice, fshipqty ) IN 'H:\db\mdb.accdb'
    SELECT Left(dbo_jomast.fjobno,5) as fjobno, Right(dbo_jomast.fjobno,4) as subno, dbo_jomast.fpartno, dbo_jomast.fpartrev, dbo_jomast.fsono, dbo_jomast.fcompany, dbo_jomast.fddue_date, dbo_jomast.fquantity, dbo_jopact.flabact + dbo_jopact.fsetupact as flabact, dbo_jopact.fmatlact+dbo_jopact.fsubact + dbo_jopact.ftoolact + dbo_jopact.fothract as fmatlact, dbo_jopact.fovhdact as fovhdact, dbo_joitem.fgroup, dbo_joitem.fuprice as jfuprice, dbo_joitem.fshipqty as jfshipqty, dbo_sorels.funetprice as fuprice, dbo_sorels.fshipbook + dbo_sorels.fshipmake + dbo_sorels.fshipbuy as fshipqty
    FROM dbo_jomast
    INNER JOIN dbo_joitem ON dbo_joitem.fjobno = dbo_jomast.fjobno
    INNER JOIN dbo_jopact ON dbo_jopact.fjobno = dbo_joitem.fjobno
    INNER JOIN dbo_jopest ON dbo_jopest.fjobno = dbo_jopact.fjobno
    LEFT OUTER JOIN dbo_inmast ON dbo_inmast.fpartno = dbo_jomast.fpartno AND dbo_inmast.frev=dbo_jomast.fpartrev AND dbo_inmast.fac=dbo_jomast.fac
    LEFT OUTER JOIN dbo_sorels ON dbo_sorels.fsono=dbo_jomast.fsono AND dbo_sorels.fstatus = dbo_jomast.fjobno
    WHERE dbo_jomast.fopen_dt Between '12/31/2007' And '7/2/2008'
    "


    It appears that access does not like the way I am writing my join statements... what syntax do i need to change?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •