Solved

VBA Import Access Table

Posted on 2010-11-30
4
563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

751 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