Solved

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

Posted on 2010-08-30
18
856 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 125 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now