Solved

VBA Import Access Table

Posted on 2010-11-30
4
560 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:Seamus2626
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34239315
0
 

Author Comment

by:Seamus2626
ID: 34239779
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
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 34245482
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
 

Author Closing Comment

by:Seamus2626
ID: 34247913
Perfect, thaks Ed!!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvieā€¦
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

809 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