Solved

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

Posted on 2010-08-30
18
847 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

762 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

19 Experts available now in Live!

Get 1:1 Help Now