Solved

Download Table via ADO to import to Access

Posted on 2011-09-13
6
265 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
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now