Using Pass-Through with a Temp Table 2

[cghoga]

Hello,

I have a SQL procedure that creates a temp table initially and then runs several updates to columns within the table.

At the end I query the table for the result set.

I have used a pass through before but never quite like this.

Here is where I am at this point...

I ran the same SQL script in the Access pass-through that I can successfully run in SQL and it did not error out, but my insert statement (VBA) did not insert any records. There was not any data in the table in Access.

How do I get Access to recognize the temp table so that I can insert the values into a table within Access to be viewable in a form or report?

Any and all help is greatly appreciated.

Thanks!

 

[PWise]

what vba code do you have?

 

[cghoga]

Thanks pwise, please see below...

Dim myDb As DAO.Database
Dim myQd As DAO.QueryDef
Dim connectstr(1) As String
Dim sObjStr As String

DoCmd.SetWarnings False

DoCmd.DeleteObject acQuery, "BaseQuery"

DoCmd.RunSQL "delete from TestTable"

sObjStr = "TestQuery"

'define connections to ODBC tables
connectstr(1) = "ODBC;DSN=Corpdb05;UID=hoganc;Trusted_Connection=Yes"

'Setup the base script properties
Set myDb = CurrentDb
Set myQd = myDb.CreateQueryDef("BaseQuery")
myQd.ODBCTimeout = 1000
myQd.Connect = connectstr(1)
myQd.ReturnsRecords = True

myQd.SQL = myDb.QueryDefs(sObjStr).SQL

myQd.Connect = connectstr(1)
DoCmd.RunSQL "INSERT INTO TestTable ( employer, company, account ) SELECT employer, company, account FROM BaseQuery"

myQd.Close

DoCmd.SetWarnings True

 

[cghoga]

And here is an example of the SQL in the pass through...

Select employer, company, account
into #Test
from Table1

Select employer, company, account
from #Test

This is very basic I know. This is just the first step. Subsequent steps will require updates to the temp table. I wanted to build the infrastructure upfront and get it going before I tackled the whole process.

Thanks again for your help....

 

[PWise]

when you run this qurey

Select employer, company, account
into #Test
from Table1

Select employer, company, account 
from #Test

it returns records ??


I tried it and i got a message box

[qoute]

pass through query with returns records set to true did not return any records did not return any records

[/quote ]

 

[cghoga]

It does not return records.

If I comment out the '--into #Test' and the last row and just do a simple select statement it populates the recordset and my table.

It's like it's not recognizing the final select from the temp table.

 

[cmmrfrds]

A temp table only stays in scope on the same connection. It looks to me like a 2nd connection is being started to retrieve from the temp table. Stay with the 1st connection.

 

[cghoga]

Are you saying this cannot be done or is there a way to stay with the 1st connection?

I tried removing the second instance of 'myQd.Connect = connectstr(1)' and it did not help.

Thanks for the suggestions.

 

[cmmrfrds]

I have made use of temp tables through an ADO connection and I think you can through DAO, but I don't know if it can be done using Macros like DoCmd.RunSQL. Somebody that uses DAO a lot could show you how to do it avoiding Macros. I could give you an example using ADO but that introduces additional learning.

 

[PWise]

try just this

dim mydb as database
set mydb=currentdb
currentdb.execute "INSERT INTO TestTable ( employer, company, account )Select employer, company, account from Table1  IN '' [ODBC;DSN=Corpdb05;UID=hoganc;Trusted_Connection=Yes];"

 

[cghoga]

I understand. I am willing to learn if you coud get me started down the path.

Thanks for your help.

 

[cmmrfrds]

Here is an example of using ADO to create a connection, create a #temp table, add a record to the table, and then read the record.


Function TestTempTable()
'-- Reference Library
'-- Microsoft ActiveX data objects 2.6 library needed for ADO
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=Northwind;" & _
"Data Source=shark;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

'-- create the temporary table
Dim mykey As Integer, sql1 As String, textData As String
textData = "Some description data"
mykey = 10
sql1 = "insert into #myTempTable values(" & mykey & ",'" & textData & "')"
cn.Execute "create table #myTempTable (akey int, muchWords ntext) "
Debug.Print sql1
cn.Execute sql1
rs.Open "Select * from #myTempTable", cn, adOpenStatic, adLockOptimistic
Debug.Print "first field = "; rs(0); " second field = "; rs(1)

''cn.Execute "drop table #myTemptable"
cn.Close
Set cn = Nothing

End Function

 

[cghoga]

Brilliant! Thanks for your time and help.

cghoga