VBA Import Access Table

Hi,

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"

Thanks
Seamus
Seamus2626Asked:
Who is Participating?
 
MINDSUPERBConnect With a Mentor Commented:
Seamus,

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 & ";"
      .Open
      .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
      .Close
   End With

   ' clean up
   Set rst = Nothing
   cnn.Close
   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

Sincerely,
Ed
0
 
MINDSUPERBCommented:
0
 
Seamus2626Author Commented:
Thanks MINDSUPERB,

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?

Thanks
Seamus


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
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub
0
 
Seamus2626Author Commented:
Perfect, thaks Ed!!
0
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.

All Courses

From novice to tech pro — start learning today.