Excel-VBA

[Dara]

Hi
Am new to excel programming.I have created one table known as tab1 with 5 fields using ms access.In the excel sheet i inserted one button.
I need a macro such that when i click the button in the excel sheet,i need the datas from the table which i have created in access to be displayed in the excel sheet.I think we can write sql query in the macro to bring all the columns in excel sheet.Please help me to do this

[Bob Phillips]

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
Range("A1") = ary
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

[mancubus]

hi and wellcome to VBAX.

try this with a copy of your file...


Option Explicit

Sub Import_AccessData()

'http://www.ozgrid.com/forum/showthread.php?t=11285

'You need to set a reference to Microsoft ActiveX Data Objects x.x library
'Microsoft ActiveX Data Objects Recordset Objects x.x library, Microsoft Access XX.0 Object Library, etc
'via Tools | Reference... in the VB-editor.

Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wsSheet As Worksheet
Dim lnNumberOfField As Long, lnCount As Integer

Set wsSheet = ThisWorkbook.Worksheets("Sheet1") 'change Sheet1 to your sheet name

stDB = ThisWorkbook.Path & "\" & "XLData.mdb" 'chage XLData.mdb to your db name
'assuming excel and access files are in the same folder.
'otherwise stg like this:
'stDB = "C:\Users\xxxx\MyFiles\XLData.mdb"

wsSheet.Range("A1").CurrentRegion.Clear

cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & stDB & ";"
'for office 2007-2010:
'cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & stDB & ";"
rst.Open "SELECT * FROM tblName", cnt 'change tblName to your table name

lnNumberOfField = rst.Fields.Count
For lnCount = 0 To lnNumberOfField - 1
wsSheet.Cells(1, lnCount + 1).Value = rst.Fields(lnCount).Name
Next lnCount

wsSheet.Cells(2, 1).CopyFromRecordset rst

Set rst = Nothing
Set cnt = Nothing

End Sub

[Dara]

Hi mancubus,
I tried ur code.Am getting an error as user-defined type not defined in the line Dim cnt As New ADODB.Connection.

[Kenneth Hobs]

'You need to set a reference to Microsoft ActiveX Data Objects x.x library 'Microsoft ActiveX Data Objects Recordset Objects x.x library, Microsoft Access XX.0 Object Library, etc 'via Tools | Reference... in the VB-editor.