?
Solved

Download Table via ADO to import to Access

Posted on 2011-09-13
6
Medium Priority
?
308 Views
Last Modified: 2012-05-12
I have created a DSN-less connection to my SQL Server located on the web.  I have created a recordset with that connection (the top 1000 records to save time).

I'd like to make a table from this recordset.  Is there a way I can import the recordset from memory to the table on disk OR import a table from the connection to a table on disk.

What I want to avoid is having to build the table structure and then import record by record.  It seems less versatile than importing the existing table structure.

Thanks,
JOe K.
Dim CN As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim sSQL As String
    
    CN.CursorLocation = adUseClient
    
    'this works I'm just keeping out the private info
    CN.ConnectionString = "Driver={SQL Server Native Client 10.0};Server=MyServ.com;Database=myDb;Uid=UserN;Pwd=Pw;"
    CN.Open
        
    sSQL = "SELECT TOP 1000 * FROM dbo.tblQuib"
    RS.Open sSQL, CN, adOpenStatic, adLockOptimistic
    
    'HOW DO I CREATE THE TABLE FROM THE RecordSet in Memory?
    'below does not work
    'CurrentDb.Execute "CREATE TABLE MyTable FROM " & RS.Source

Open in new window

0
Comment
Question by:ClaudeWalker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 668 total points
ID: 36530520
If you're going to use ADO and don't want to import row by row, then you might dump the table out to a file and then import that file to Access.


See link for alternative imports from SQL Server to Access.
http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx


You also may try to link to SQL Server from Access and do a "select..insert"

0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 668 total points
ID: 36530601
an alternative way is to export the records to an excel file and import the excel file


dim xlObj as object, sheet as object
dim iRow,iCol,j
set xlObj=createobject("excel.application")
      xlObj.workbooks.add
Set Sheet = xlObj.activeworkbook.Sheets(1)

' populate the created excel file
irow=1
    For iCol = 0 To rs.Fields.Count - 1
        Sheet.cells(iRow, iCol + 1).Value = rs.Fields(iCol).Name
    Next


Sheet.Range("A2").CopyFromRecordset rs   ' copy the data

xlobj.activeworkbook.saveas "c:\myExcel.xls"

xlobj.quit


0
 
LVL 40
ID: 36530680
Why go through ADO (a COM technology), Access and a DNS to link to SQL Server, when there are classes in ADO.NET that go straigth to the server and do not require the user to have Access installed on its computer?

Sure, you will need to learn a new way of working since ADO.NET has big differences with old classic ADO, but you will probably need to make the switch anyway eventually if your organization uses SQL Server. In between, you will gain performance, enhanced possibilities, and prevent the configuration issues that always get into the workflow when you try to use 3 intermediates to get somewhere.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Assisted Solution

by:x77
x77 earned 664 total points
ID: 36530692
I do it on .Net.

There are some task to do this.

 1 - You can create each field on MsAccess Database Table From Field Definition on Original RecordSet.
      For String Field you need convert Fields with length > 255 to Memo in Access.

2 - Open Destination RecordSet in MsAccess Created Table.

3 - Loop over original Recordset until eof
         For each record, copy all field values to MsAccess New Row and save record.

4 - Close Recordsets

5 - Optional, Create Index for new table.

 
'Creación Columnas para Microsoft Access  
    Public Function MsAccessNewTable (ByVal Db As Database, ByVal Name As String, ByVal DT As DataTable) As TableDef
        Dim T = db.CreateTableDef (Name)
        For Each c As DataColumn In DT.Columns
            If "ROWID".Equals (c.ColumnName) = False Then _
                MSAccessFld (T, Type.GetTypeCode (c.DataType), c.ColumnName, c.MaxLength)
        Next
        Db.TableDefs.Append (T)
        Return T
    End Function

    Public Function MSAccessFld (ByVal TD As TableDef, ByVal Tc As TypeCode, ByVal ColumnName As String, _
                                 Optional ByVal MaxLength As Integer = - 1) As Field
        Dim FLD As Field = Nothing
        Select Case Tc
            Case TypeCode.String
                FLD = TD.CreateField (ColumnName, If(MaxLength > 255 OrElse MaxLength < 0, _
                                                     DataTypeEnum.dbMemo, DataTypeEnum.dbText), _
                                      If(MaxLength > 0, CObj (MaxLength), Missing.Value))
            Case TypeCode.DateTime : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbDate)

            Case TypeCode.Int16 : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbInteger)
            Case TypeCode.Int32 : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbLong)

            Case TypeCode.Single : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbSingle)
            Case TypeCode.Double : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbDouble)

            Case TypeCode.Decimal : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbDouble)

            Case TypeCode.Char : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbText, 1)
            Case TypeCode.Boolean : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbBoolean)
            Case TypeCode.Byte : FLD = TD.CreateField (ColumnName, DataTypeEnum.dbByte)
            Case Else : Throw New Exception (Tc.ToString & " Tipo no Implementado, Columna: " & ColumnName)
        End Select
        TD.Fields.Append (FLD)
        Return FLD
    End Function

Open in new window


Note that I use DAO also, but I need convert from TipeCode to Ado.FieldType using Select Case.
You only need check for compatibility Field Types  (string > 255, Boolean type ...)

 
DT = TdPos : 
            T = MsAccessNewTable(db, "TdPos", DT)
            rs = T.OpenRecordset(dao.RecordsetTypeEnum.dbOpenTable, dao.RecordsetOptionEnum.dbAppendOnly)
            NCols = TdPos.Columns.Count - 1
            For Each R In TdPos.Rows
               rs.AddNew()
               For N = 0 To NCols : rs(N).Value = R(N) : Next
               rs.Update()
            Next
            rs.Close()

'Optional
            Dim rpos = db.CreateRelation("TdPos", "Td", "TdPos", _
              dao.RelationAttributeEnum.dbRelationDontEnforce Or _
              dao.RelationAttributeEnum.dbRelationLeft)
            With rpos
               Dim fld = .CreateField("CodTra")
               fld.ForeignName = "CodTra"
               .Fields.Append(fld)
            End With
            db.Relations.Append(rpos)

Open in new window


Note that I use Ado.Net and also Dao :   rs(N).Value = R(N)
For DAO, you use:      rs(N).Value = R(N).Value
0
 
LVL 15

Expert Comment

by:x77
ID: 36530723
About Using Dao with MsAccess.

I Create a New DataBase and fill four Tables with about 20000 rows and create relations an indexes in about 1 to 2 seconds (About 4 Mb DataBase).

Ms Access DAO is very fast !!
0
 

Author Closing Comment

by:ClaudeWalker
ID: 36561689
I ended up importing to a recordset in memory and then added each one record by record.  That's not saying the other solutions wouldn't work but I didn't try them.

Thanks for the suggestions.  I was able to connect avoiding using a DSN but the process is overly complicated and far from versital (that's no fault of anyones but mine).  However, the job is complete and it definitely serves it's purpose.

Again thanks for the help,
JOe K.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question