create local access table from ADODB sqloledb recordset

[SteveApa]

Hi All.
I have searched, but have not found what I am looking for. I am experienced with VB and DAO - now I just started with ADODB. Our IT dept created an excel file for us to use to get data from a server database. They set it up with a button to refresh data from a server database. I want to connect to the database directly from access and skip their excel file. I used their excel file to give me the connection info (and this forum with google).

So, I have made the connection to the database and have a recordset opened. How do I put the data from the recordset into a local access table??

The table has qty 460 fields and 5030 rows. I know access will only let me create a table with max 255 fields - or - I just pick the ones I want with sql

Public Sub test()Dim i As Integer
Dim sqltxt As String
Dim rs As ADODB.Recordset
Dim cn As New ADODB.Connection


    cn.Provider = "sqloledb.1"
    cn.Properties("Data Source").Value = "xxxxxx.com" ' I hid the real address
    cn.Properties("Initial Catalog").Value = "IDB"
    cn.Properties("Integrated Security").Value = "SSPI"
    cn.Open
    sqltxt = "select [Project Number] from Raw_Data_SV" ' this gets individual fields vs select * from Raw_Data_SV would get me all qty 460 fields
    Set rs = cn.Execute(sqltxt)


' the recordset is now open - I have verified values in the immediate window.  How do I write it to a local access table?


    rs.MoveFirst
    i = 0
    While Not rs.EOF ' I just did this loop to look thru the recordset
        i = i + 1
        rs.MoveNext


    Wend
    cn.Close


End Sub

Thanks!
Steve

[CJ_London]

something like

While Not rs.EOF ' I just did this loop to look thru the recordset
            currentdb.execute("INSERT INTO localtbl (fld1, fld2) VALUES(" & rs!fld1 & ", " & rs!fld2 & ")")
        rs.MoveNext

Wend

change names to suit table and field names - and modify the values section to include single quotes for text fields and # for date fields. If date fields are in the format dd/mm/yyyy they need to be formatted US style as mm/dd/yyyy.