Solved

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

Posted on 2010-08-30
18
880 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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.

742 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