Create a table from a recordset 1

[redapples]

Can it be done?

I have a recordset from a query in another database that I wish to be able to save as a table in my current database.

I have used ADO see code below but am not sure how to move forward with this.

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strFilePathForConnection As String
    Dim qdReport
    Dim cat As ADOX.catalog
    
    strFilePathForConnection = "C:\Documents and Settings\redapples.domain\Desktop\temp\Core data Rough Sleepers V5.mdb"
    cnn.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strFilePathForConnection, "admin", , -1
    rs.Open "qryIndividualsBiographicalRS", cnn, 3, 3
    
Ok up to here!


    Set cat = New ADOX.catalog
    Set cat.ActiveConnection = cnn
    Set qdReport = New ADODB.Command
    qdReport = rs
    cat.Tables.Append qdReport
    cat.Tables.Append rs

Just trying things here but not sure where to go.

One problem I have is I do not have any help files in Access for ADO or ADOX (not sure why) so I cant check any of my syntax or methods.

Any help really welcome.

Thanks

Redapples

Want the best answers? See FAQ181-2886

 

[DeanWilliams]

Hello,

Do you have to create this table from this recordset?

You can just create a make-table query and browse to this other database.

I never really use ADO so am not that well versed in it. I am still a DAO man myself.

Dean.

 

[redapples]

I dont mind a DAO version. Although would prefer ADO. The recordset in question is based upon a query in the remote DB would this work with a query? If so give me the method to do this in code and I will try it out.

Red.

Want the best answers? See FAQ181-2886

 

[cmmrfrds]

If you have a reference set to the ADO library, then right click on ADODB on the next line, then select definition and it will bring up the object browser.

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim MySQL as String

'Assuming you want table in current app. Also, assuming Access 2000 or >.
Set cnn = CurrentProject.Connection

MySQL = "Insert into yourtable (Select * From Pathtoyourothermdb.mdb.othertablename)"
'Assumes same columns in both tables.

'With another Access mdb, you can reference right in the sql statement with fully qualified path and table name.

cnn.Execute MySQL

 

[redapples]

cmmfrds,
thanks for putting me on the right track your SQL was a bit off, here is my version.

MySQL = "INSERT INTO tblIndBioAcc SELECT qryIndividualsBiographicalRS.* FROM qryIndividualsBiographicalRS IN 'C:\Documents and Settings\redapples.domain\Desktop\temp\Core data Rough Sleepers V5.mdb';"

But got it cracked anyway.

For the sake of curiosity (should anyone read this post) Is my origional question possible can a recordset be used to make a table? One possible way I thought of - some time in the middle of the night - was to cycle through each record but this seem really cumbersome and there must be a better method.

Redapples

Want the best answers? See FAQ181-2886

 

[p27br]

do you have access to the internet ?
then check out these articles :

http://msdn.microsoft.com/library/d...y/en-us/ado270/htm/pg_adox_fundamentals_1.asp

and

http://msdn.microsoft.com/library/default.asp?url=/code/list/ado.asp

 

[redapples]

I had seen these already but thank you anyway.

This web site gives this information for the Append Tables method:


Syntax
Tables.Append Table

Parameters
Table
A Variant value that contains a reference to the Table to append or the name of the table to create and append.

But i am not sure in real world examples how this might work.

how might the variant be created, what information can be passed with this variant? Just the name? Or other information such as field numbers or Data types for fields. The example is not clear. Has anyone ever used this method? If so can they shed some light onto the issue.


Want the best answers? See FAQ181-2886

 

[redapples]

right almost there.

Function Createtable()

    Dim rs As New ADODB.Recordset
    Dim cnn1 As New ADODB.Connection
    Dim strFilePathForConnection As String
    Dim fld As Field

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    strFilePathForConnection = "C:\Documents and Settings\sconnell.GCSH\Desktop\temp\Core data Rough Sleepers V5.mdb"
    cnn1.Open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strFilePathForConnection, "admin", , -1
    rs.Open "qryIndividualsBiographicalRS", cnn1, 3, 3

    cnn.Open CurrentProject.Connection
    Set cat.ActiveConnection = cnn

    With tbl
        .Name = "tblinbioacc"
        Set .ParentCatalog = cat
        For Each fld In rs.Fields
            .Columns.Append fld.Name, fld.Type
        Next
    End With

    cat.Tables.Append tbl

    Set cat = Nothing

End Function

This will replicate a table, a bit more would get the data from rs into tbl I guess. Having done all that, I am not sure where I would ever use this as I have now managed to accomplish what I set out to do (with less code too - See above). Nice to nail a solution though. Poached a bit of the code from thread "Change data type using query expression"
thread702-707664 to get here.

Want the best answers? See FAQ181-2886