Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBA Import Access Table

Posted on 2010-11-30
4
Medium Priority
?
573 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

577 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