[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Download Table via ADO to import to Access

Posted on 2011-09-13
6
Medium Priority
?
314 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
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!

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

649 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