VBA Import Access Table

Posted on 2010-11-30
Medium Priority
Last Modified: 2012-05-10

Does anyone have the VBA excel code to import an Access Table and then choose not to wrap text

The table is called "Internal Accounts Report"

It needs to get called into worksheet "Raw Data" in my workbook "Internal Accounts Report"

Question by:Seamus2626
  • 2
  • 2
LVL 19

Expert Comment

ID: 34239315

Author Comment

ID: 34239779

Ive put the code in and the reference like below.

Im getting "Expected end of Statement" on "G:\Seamus\Triple Link.mdb"

Do i need to alter the code anymore?


Sub ADOImportFromAccessTable "G:\Seamus\Triple Link.mdb", _
    "Internal Accounts Raw Data", ("A1))
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
    "TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
        ' open the recordset
        .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
        ' all records
        '.Open "SELECT * FROM " & TableName & _
            " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText
        ' filter records
        RS2WS rs, TargetRange ' write data from the recordset to the worksheet
'        ' optional approach for Excel 2000 or later (RS2WS is not necessary)
'        For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
'            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
'        Next
'        TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    Set rs = Nothing
    Set cn = Nothing
End Sub
LVL 19

Accepted Solution

MINDSUPERB earned 2000 total points
ID: 34245482

Try to use this code instead:

Sub GetAccessData()
   ' Sample demonstrating how to return a recordset from an Access db
   ' late bound so does not require a reference to the Microsoft ActiveX Data Objects Library.
   Dim cnn As Object, strQuery As String, rst As Object
   Dim strPathToDB As String, strFormula As String, i As Long
   Dim wks As Worksheet
   ' output to activesheet
   Set wks = ActiveSheet
   ' Path to database
   strPathToDB = "G:\Seamus\Triple Link.mdb"
   Set cnn = CreateObject("ADODB.Connection")
   ' open connection to database
   With cnn
      .ConnectionTimeout = 500
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" & strPathToDB & ";"
      .CommandTimeout = 500
   End With
   ' SQL query string - change to suit
   strQuery ="Internal Accounts Raw Data",
   ' create new recordset
   Set rst = CreateObject("ADODB.Recordset")
   ' open recordset using query string and connection
   With rst
      .Open strQuery, cnn
      ' check for records returned
      If Not (.EOF And .BOF) Then
         'Populate field names
         For i = 1 To .Fields.Count
            wks.Cells(1, i) = .Fields(i - 1).Name
         Next i
         ' Copy data starting from A2
         wks.Cells(2, 1).CopyFromRecordset rst
      End If
   End With

   ' clean up
   Set rst = Nothing
   Set cnn = Nothing
End Sub

You may refer to this thread as well. http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26206854.html


Author Closing Comment

ID: 34247913
Perfect, thaks Ed!!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

607 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