Extracting Data from Access dB to Excel, using VBA

[Clarkyboy420]

Hi All,

Firstly, I'm an amateur on a steep learning curve, so please excuse me if
some of my terminology/understanding is incomplete.

I have found a VBA procedure which will extract entire tables from a db to a
grid array in an excel spreadsheet. The procedure relies on two cell values,
one identifies the database path, the other identifies the required table.
When the procedure is run, it evaluates the two values and returns the
specified table in its entirety.

The problem I am having is that it works perfectly for tables that have no
'dead-space', ie blank fields, however, if there is even one blank field/cell
in the db table, then the VBA procedure fails to return any data whatsoever.

I have been led to believe that the issue is I need a 'null handling'
section in my procedure, but have to admit I have no idea how to begin
coding this at my present knowledge level.

I would greatly appreciate any help with this one. Massive thanks in advance.

Chris Clark

Please find below my current procedure;

Function GetInfoFromAccess( _
sDBFullName As String, _
sTableName As String) As Variant

Dim vResult As Variant
Dim oCN As ADODB.Connection, oRS As ADODB.Recordset

' open the database
Set oCN = New ADODB.Connection
Dim sCNString As String
sCNString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
sDBFullName & ";"
oCN.Open sCNString

Set oRS = New ADODB.Recordset
With oRS
' all records
Dim sSelectString As String
sSelectString = "SELECT * FROM " & sTableName & ";"
.Open sSelectString, oCN

vResult = oRS.GetRows ' gets all the rows from the data returned
End With

' close down recordset & connection
oRS.Close
Set oRS = Nothing

oCN.Close
Set oCN = Nothing

GetInfoFromAccess = vResult
End Function

 

[RB Smissaert]

Just a quick guess, but try this:

sSelectString = "SELECT * FROM [" & sTableName & "];"
instead of:
sSelectString = "SELECT * FROM " & sTableName & ";"


RBS

 

[EricG]

I tried calling your function using a command button I put on "Sheet1",
placing the data on "Sheet2", using the following code (placed in "Sheet1").
All the data showed up, including the (many) fields in my own database table
that had NULL values in them.

The only change I made to your function was to change the provider to:
"Provider=Microsoft.Jet.OLEDB.4.0;"

I'm not sure whether that makes a differenc or not. My code:

Dim theData() As Variant

Private Sub CommandButton1_Click()
Dim theDBName As String
Dim theTable As String
Dim nRows As Long, nCols As Long
'
theDBName = "d:\data\myPath\myDatabase.mdb"
theTable = "myTable"
'
theData = GetInfoFromAccess(theDBName, theTable)
nRows = UBound(theData, 2)
nCols = UBound(theData, 1)
Sheets("Sheet2").Select
ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(nCols + 1,
nRows + 1)) = theData
End Sub

HTH,

Eric

 

[RB Smissaert]

Ignore this post.
I somehow thought you were talking about spaces in the table name.

RBS

Just a quick guess, but try this:

sSelectString = "SELECT * FROM [" & sTableName & "];"
instead of:
sSelectString = "SELECT * FROM " & sTableName & ";"


RBS

Hi All,

Firstly, I'm an amateur on a steep learning curve, so please excuse me if
some of my terminology/understanding is incomplete.

I have found a VBA procedure which will extract entire tables from a db
to a
grid array in an excel spreadsheet. The procedure relies on two cell
values,
one identifies the database path, the other identifies the required
table.
When the procedure is run, it evaluates the two values and returns the
specified table in its entirety.

The problem I am having is that it works perfectly for tables that have
no
'dead-space', ie blank fields, however, if there is even one blank
field/cell
in the db table, then the VBA procedure fails to return any data
whatsoever.

I have been led to believe that the issue is I need a 'null handling'
section in my procedure, but have to admit I have no idea how to begin
coding this at my present knowledge level.

I would greatly appreciate any help with this one. Massive thanks in
advance.

Chris Clark

Please find below my current procedure;

Function GetInfoFromAccess( _
sDBFullName As String, _
sTableName As String) As Variant

Dim vResult As Variant
Dim oCN As ADODB.Connection, oRS As ADODB.Recordset

' open the database
Set oCN = New ADODB.Connection
Dim sCNString As String
sCNString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
sDBFullName & ";"
oCN.Open sCNString

Set oRS = New ADODB.Recordset
With oRS
' all records
Dim sSelectString As String
sSelectString = "SELECT * FROM " & sTableName & ";"
.Open sSelectString, oCN

vResult = oRS.GetRows ' gets all the rows from the data returned
End With

' close down recordset & connection
oRS.Close
Set oRS = Nothing

oCN.Close
Set oCN = Nothing

GetInfoFromAccess = vResult
End Function