Reading ADO rs with DAO

[Brian]

Thanks for your suggestion.

The only problem i have with it is that although I would
not be DNS dependent, I still have to have the ODBC
drivers installed on the client machine. Worst yet is that
even if I use the well distributed Microsoft ODBC for
Oracle driver, Oracle 8i requires a TNS.ora file corectly
configured and placed on every machine that would use this
Access Database to connect to the Oracle dB

Because I shape the data quite a bit from five base tables
before it ends up in a report, I wanted to create temp
local Access tables for speed and performance. I could
make several ADO connections and shape the data in memory,
but would prefer local tables.

So far in my research ADO can create persistent local
recordsets, but only in XML or a propriatory format called
called ADGT. Access 97 does not have XML capability and
ADGT is propriatory.

Any further pointers would be appreciated

-----Original Message-----
Why not see whether you can modify the code at
http://members.rogers.com/douglas.j.steele/DSNLessLinks.ht ml to work with
Oracle (my example is for SQL Server)? Then your linked tables won't require
a DSN.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)






.

..

 

[Douglas J. Steele]

ADO still needs drivers to be installed on the client.

For one approach to local tables, see what Tony Toews has at
http://www.granite.ab.ca/access/temptables.htm

 

[Pieter Wijnen]

something like:

Option Compare Database
Option Explicit

Function MakeParm(Fld As DAO.Field) As String
Dim Ret As String
Ret = "[" & Fld.Name & "#] "
Select Case Fld.Type
Case DAO.dbChar, DAO.dbText
Ret = Ret & "TEXT(" & Fld.Size & ")"
Case DAO.dbTime, DAO.dbTimeStamp, DAO.dbDate
Ret = Ret & "DateTime"
Case Else ' find out the rest for yourselves
Ret = Ret & "Double"
End Select
MakeParm = Ret


End Function

Function ADOToDAOType(ADOType As Long) As Long
Dim Ret As Long
Select Case ADOType
Case ADODB.adDate, ADODB.adDBTime, ADODB.adDBDate
Ret = DAO.dbDate
Case ADODB.adChar, ADODB.adVarChar, ADODB.adVarWChar
Ret = DAO.dbText
Case Else ' Assume numeric (double) (add as needed)
Ret = DAO.dbDouble
End Select
ADOToDAOType = Ret

End Function
Sub CreateLocal()
Dim AConn As New ADODB.Connection
Dim ACmd As New ADODB.Command
Dim ARs As ADODB.Recordset
Dim AFld As ADODB.Field

Dim DDb As DAO.Database
Dim DFld As DAO.Field
Dim DTdef As DAO.TableDef
Dim DQdef As DAO.QueryDef
Dim Dprm As DAO.Parameter

Dim insstr As Variant, Valstr As Variant, Parmstr As Variant
Dim i As Integer

Set DDb = Access.CurrentDb()
AConn.ConnectionString = "...."
AConn.Open
Set ACmd.ActiveConnection = AConn
ACmd.CommandText = "SELECT A.* FROM COUNTRY A"
Set ARs = ACmd.Execute
Set DQdef = DDb.CreateQueryDef("")
Set DTdef = DDb.CreateTableDef("tmpCountry")
Valstr = Null
insstr = Null
Parmstr = Null
For Each AFld In ARs.Fields
Set DFld = DTdef.CreateField(AFld.Name, ADOToDAOType(AFld.Type),
AFld.DefinedSize)
DTdef.Fields.Append DFld
insstr = (insstr + ",") & DFld.Name
Valstr = (Valstr + ",") & "[" & DFld.Name & "#]" ' use parameters
Parmstr = (Parmstr + ",") & MakeParm(DFld)

Next

DDb.TableDefs.Append DTdef
DDb.TableDefs.Refresh

insstr = "PARAMETERS " & Parmstr & ";" & _
vbCrLf & "INSERT INTO " & DTdef.Name & " (" & insstr & ")" & _
vbCrLf & "VALUES(" & Valstr & ")"
DQdef.SQL = insstr
'DQdef.Prepare()
' Parameters pos match Fields so..
While Not ARs.EOF
For i = 0 To ARs.Fields.Count - 1
DQdef.Parameters(i).Value = ARs.Fields(i).Value
Next
DQdef.Execute
ARs.MoveNext
Wend

Set DQdef = Nothing
ARs.Close: Set ARs = Nothing
Set ACmd = Nothing
Set AConn = Nothing
Set DTdef = Nothing
Set DDb = Nothing
End Sub

HTH

Pieter