Help, export database using ADO

[JDennis]

I'm trying to export the result from a
Select Sql statement into a DBF file using ADO.

I try with a control data, but the result from my select sql go only in a recordsource.

How do we do?

JDennis

[Chris]

How about DAO? I did this in previous post thread.

[Klass]

Hi JDennis, here’s a bit of a long round about way of doing it.

I just slapped this together and it’s not saving to a DBF but rather an MDB.

The Recordsource is coming from an ADO Data Control. I wasn’t able to figure out how to retrieve the table info using ADO but I’m sure there’s a way to do it.

Private Sub Form_Load()
Call CreateDB
Call CreateTable
Call PopFields
End Sub

******************************************************
‘Create the Database
Sub CreateDB()
Dim cat As New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=e:\new.mdb"
End Sub

*************************************************
‘ Create the table and add the Columns.
Sub CreateTable()
Dim tbl As New Table
Dim cat As New ADOX.Catalog


cat.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Data Source=e:\new.mdb;"

tbl.Name = "SavedSel"
tbl.Columns.Append "FIRSTNAME", adChar
tbl.Columns.Append "SECONDNAME", adChar
cat.Tables.Append tbl
End Sub

***********************************
‘Populate the Fields
Sub PopFields()
Dim cnn1 As ADODB.Connection
Dim rstSavedSel As ADODB.Recordset
Dim strCnn As String
Dim strFirstName As String
Dim strSecondName As String

Set cnn1 = New ADODB.Connection
strCnn = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=E:\new.mdb"
cnn1.Open strCnn

Set rstSavedSel = New ADODB.Recordset
rstSavedSel.CursorType = adOpenKeyset
rstSavedSel.LockType = adLockOptimistic
rstSavedSel.Open "SavedSel", cnn1, , , adCmdTable

Adodc1.Recordset.MoveFirst
Do While Not Adodc1.Recordset.EOF
strFirstName = Adodc1.Recordset.Fields.Item(1).Value
strSecondName = Adodc1.Recordset.Fields.Item(2).Value

rstSavedSel.AddNew
rstSavedSel!FIRSTNAME = strFirstName
rstSavedSel!SECONDNAME = strSecondName
rstSavedSel.Update

Adodc1.Recordset.MoveNext
Loop

rstSavedSel.Close
cnn1.Close
End Sub