Solved

Download Table via ADO to import to Access

Posted on 2011-09-13
6
278 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

919 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