Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Use Pass-Through query to append table in MS Access 2007

Posted on 2010-08-30
18
Medium Priority
?
898 Views
Last Modified: 2012-05-10
Hello experts,
Is it possible in MS Access 2007 to utilize Pass-through query to insert record in MS Access table.
What i have is an MS Access DB which had an ODBC access to OMNIS database. I could query OMNIS DB using passthrough but was thinking of extending it further by just calling a macro that runs the passthrough query that inserts record into my table in MS Access db.
0
Comment
Question by:jsuanque
  • 10
  • 8
18 Comments
 
LVL 13

Expert Comment

by:nike_golf
ID: 33557119
If I follow what your trying to do, I think you can create a form with a button and from that button call a macro that runs your pass through query.

You could also use VBA but would need to kick it off someway, maybe from another piece of code.

NG,

0
 

Author Comment

by:jsuanque
ID: 33557150
That is the general idea but i guess what i wanted to know if  possible the syntax...
I mean could i just create a SQL in MS Access (as a passthrough query) like this


SELECT *
INTO MyAccessDB_Table
FROM Table ---> Table from external db for thsi case OMNIS DB.
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33557191
If you have an ODBC connection and the rights to run them on OMNIS, which I'm not familiar with, yes it should work. A PASS through is nothing more than a regular SQL statement just run on another DB, the SQL might need some tweaking due to Access's language.

Check the bottom of this link for an example on SQL Server: http://www.mssqltips.com/tip.asp?tip=1482

Ng,

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jsuanque
ID: 33557387
Hello NG,
It's were i'm stucked at the moment  with just this SQL, i could generate an output.
SELECT OMNIS.fStudent.StuRecID,
             OMNIS.fStudent.StuYrgRecID,
             OMNIS.fStudent.StuNCYearGroupRecID,
             OMNIS.fStudent.StuClsRecID,
             OMNIS.fStudent.StuRollStatus,
             OMNIS.fStudent.StuReference,
             OMNIS.fStudent.StuUPN,
             OMNIS.fStudent.StuSurname,
             OMNIS.fStudent.StuFirstname,
             OMNIS.fStudent.StuSecondName,
             OMNIS.fStudent.StuThirdName,
             OMNIS.fStudent.StuKnownName,
             OMNIS.fStudent.StuLegalSurname,
             OMNIS.fStudent.StuDOB,
             OMNIS.fStudent.StuSex,
             OMNIS.fStudent.StuEntryDate,
             OMNIS.fStudent.StuLeaveDate
FROM OMNIS.fStudent

BUT, when i include
INTO MyTable
...i'm getting an "ODBC Call failed Syntax error in SQL Statement"

SELECT OMNIS.fStudent.StuRecID,
             OMNIS.fStudent.StuYrgRecID,
             OMNIS.fStudent.StuNCYearGroupRecID,
             OMNIS.fStudent.StuClsRecID,
             OMNIS.fStudent.StuRollStatus,
             OMNIS.fStudent.StuReference,
             OMNIS.fStudent.StuUPN,
             OMNIS.fStudent.StuSurname,
             OMNIS.fStudent.StuFirstname,
             OMNIS.fStudent.StuSecondName,
             OMNIS.fStudent.StuThirdName,
             OMNIS.fStudent.StuKnownName,
             OMNIS.fStudent.StuLegalSurname,
             OMNIS.fStudent.StuDOB,
             OMNIS.fStudent.StuSex,
             OMNIS.fStudent.StuEntryDate,
             OMNIS.fStudent.StuLeaveDate
INTO fStudent
FROM OMNIS.fStudent
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33557952
I think that should be an INSERT INTO..

INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

http://office.microsoft.com/en-us/access-help/insert-into-statement-HP001032245.aspx?CTT=5&origin=HP001032266
0
 

Author Comment

by:jsuanque
ID: 33558434
Hmnn...still could not make it work, I'm somehow missing something in the syntax  or maybe it's something else entirely.
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33559158
By chance have you tried writing records to the table in OMNIS through TOAD or some other application where you can run SQL?
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33563233
Any progress to report?

0
 

Author Comment

by:jsuanque
ID: 33567275
Hello NG,
Sorry for the delay, just got back my internet.
Unfortunately, there is now way of writing anything in OMNIS objects its just a one way communication (i.e. read only). I mean you could query anything or any objects in OMNIS db but you can't write to irregardless of what TPS (e.g TOAD, etc...)
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33568199
OK, I misunderstood, I thought you were writing to OMNIS...

Does the table fStudent exist in Access already?

0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33568295
Out of curiosity try this:

SELECT
      StuRecID,
      StuYrgRecID,
      StuNCYearGroupRecID,
      StuClsRecID,
      StuRollStatus,
      StuReference,
      StuUPN,
      StuSurname,
      StuFirstname,
      StuSecondName,
      StuThirdName,
      StuKnownName,
      StuLegalSurname,
      StuDOB,
      StuSex,
      StuEntryDate,
      StuLeaveDate
INTO fStudent_new
FROM fStudent
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33568315
The FROM should be:

FROM OMNIS.fStudent


SELECT
      StuRecID,
      StuYrgRecID,
      StuNCYearGroupRecID,
      StuClsRecID,
      StuRollStatus,
      StuReference,
      StuUPN,
      StuSurname,
      StuFirstname,
      StuSecondName,
      StuThirdName,
      StuKnownName,
      StuLegalSurname,
      StuDOB,
      StuSex,
      StuEntryDate,
      StuLeaveDate
INTO fStudent_new
FROM OMNIS.fStudent
0
 

Author Comment

by:jsuanque
ID: 33575692
Hello NG,
Unfortunately, still the same syntax error. Honestly i could never see any problem with the statement but i reckon this is due to OMNIS db behaviour.
0
 
LVL 13

Expert Comment

by:nike_golf
ID: 33576407
If your SELECT statement works from within Access the problem should be on Access' side..

NG,
0
 

Author Comment

by:jsuanque
ID: 33576760
I hope its that simple but at this stage all i tried seems to be syntactically correct but for some reason nothing goes through.
0
 
LVL 13

Accepted Solution

by:
nike_golf earned 500 total points
ID: 33579618
Do you have another DB around that you could try to run a query against, ORACLE, etc.?
0
 

Author Comment

by:jsuanque
ID: 33608378
Hello NG,
Wi'll try using MS SQLServer and let you know whats the result. Anyway, really appreciate your assistance on this.
0
 

Author Closing Comment

by:jsuanque
ID: 33872352
Due to unknow reason this doesn't work in MS Access but when i moved everything to MS SQL Server 2008 everything works. Thus, instead of wasting much time we decided to migrate it to SQL Server where it works.
Thanks guys. My apologies for a long delay just did a long holiday :-)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question