Solved

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

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

 

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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