Solved

Download Table via ADO to import to Access

Posted on 2011-09-13
6
307 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 167 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 167 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
Independent Software Vendors: 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 166 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

687 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