Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data not inputting from Oracle to Access

Status
Not open for further replies.

merlynsdad

Programmer
Nov 18, 2010
175
US
I'm pulling down data from an Oracle DB into Access via VBA. The pertinent code is as follows, and is running ok:

strSQL = "INSERT INTO TEMP_DAGENT (dur_signon) SELECT " & txtDatabase & "_DTA_DAGENT.dur_signon FROM " & txtDatabase & "_DTA_DAGENT;"
Debug.Print strSQL
db.Execute strSQL

There are several identical databases in Oracle, represented by & txtDatabase &. All of them have identical _dta_dagent tables with identical fields, and the temp_dagent table in Access has identical fields for dur_signon. Problem is, even though there's data in dur_signon, it's not being dumped into Access. The immediate window is showing Oracle rotate between the various databases represented by & txtDatabase &, but my data isn't coming to Access. Any ideas as to why will be greatly appreciated. Thanks in advance.

If the square peg won't fit in the round hole, sand off the corners.
 
what happens if you take out the insert, have just the select, retrieve this into a recordset and print the value from the first field to show it is retrieving the data correctly?

Also, do your Oracle administration tools show an active connection to the database through an appropriate account, and does this account have select permissions on the appropriate tables?

John
 
I removed the primary key and it now works. Thanks.

If the square peg won't fit in the round hole, sand off the corners.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top