Extracting Data from Access dB to Excel, using VBA
 

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

RBS


"RB Smissaert" wrote in message
...
Just a quick guess, but try this:

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


RBS



"Clarkyboy420" wrote in message
...
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