[AccessD] ADO Create TableDef

Thanks!  I'll try that.  

Jeremy
 


Charlotte Foust <cfoust at infostatsystems.com> wrote:
I don't know exactly what it is you want to do, but here is an example
of how to create a table using ADOX.

Public Function CreateTableADO() 
Dim tbl As ADOX.Table
Dim cat As ADOX.Catalog

' Open the Catalog.
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = New ADOX.Table
With tbl
.Name = "MyTable"
.Columns.Append "Column1", adInteger
.Columns.Append "Column2", adInteger
.Columns.Append "Column3", adVarWChar, 50
End With
cat.Tables.Append tbl
Set tbl = Nothing
Set cat = Nothing
End Function

Charlotte Foust


-----Original Message-----
From: Jeremy Toves [mailto:itsame2000 at sbcglobal.net] 
Sent: Monday, September 19, 2005 12:09 PM
To: AccessD
Subject: [AccessD] ADO Create TableDef


I've worked with DAO for so long, I'm finally taking the plunge into
ADO. I have a database that pulls up records I need from an Oracle
database. No issues there. What I want to do now is make a dynamic
output table to push all my results to. I may be going about this the
wrong way. Does anybody have suggestions? 

Thanks,
Jeremy Toves



*************************************


Sub PullRecords()
Dim rs As Recordset
Dim cn As Connection
Dim strCn As String, strSQL As String
Dim intX As Integer, intFieldCnt As Integer

strCn = "ODBC; " _
& "Provider=MSDASQL.1; " _
& "Driver=Oracle ODBC Driver; " _
& "DBQ=tns_name; " _
& "UID=username; " _
& "PWD=password;"

strSQL = "SELECT " _
& "TableName.* " _
& "FROM " _
& "TableName " _
& "WHERE " _
& "TableName.Fieldname Like " & Chr(39) & "1234%" & Chr(39)
& ";"

Set cn = New Connection
cn.ConnectionString = strCn
cn.Open
Debug.Print cn.ConnectionString
Set rs = cn.Execute(strSQL)

intFieldCnt = rs.Fields.Count

For intX = 0 To intFieldCnt - 1
Debug.Print rs.Fields(intX).Name & ": " & rs(intX)

========>> I want to create a table based on the columns I pull
here. I'd like it to be dynamic allowing me to reuse the same process
to output the result of different source tables. Ideas? <<========

Next 

rs.Close
cn.Close
End Sub
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com