Running an EXCEL macro to retrieve...

[austenr]

Is it possible to run a macro from EXCEL that accesses an ACCESS DB without ACCESS being installed on the machine?

[xCav8r]

Yup, just use ADO or DAO.

[austenr]

What is the correct syntax for a table when referencing it in an SQL statement.
Ex. Mary Had A Lamb.
Thanks

[austenr]

Getting a run time syntax error on the table name

[xCav8r]

A basic SQL statement for getting data would be "SELECT tablename.field1, tablename.field2 FROM tablename", but paste me the relevant code, and I can be more specific.

[austenr]

if the table is names "Mary Had Lamb" would it be that way or "Mary_Had_Lamb"? without the "" of course

[xCav8r]

It's generally considered bad form to have spaces in your table names. That said, Access and SQL Server 2000 handle references to names with spaces with brackets: SELECT [Field Name With Spaces] from [Table Name With Spaces].

Hope that helps.

[austenr]

Getting the error Cannot find installable ISAM. Attached is the code.

[xCav8r]

What are the most recent versions of ADO and DAO you have? I'll reply with examples of each. Oh, and when you first asked the question, I assumed you meant read from an Access db. Looks like you're doing the opposite. Can I get a copy of the db, or do you want me to just create a sample one?

[austenr]

Here ya go...Not sure what versions I have how do you tell?

[xCav8r]

Look in the Visual Basic Editor under the Tools menu for References. I think your workbook had ADO 2.1 referenced. So you'll see in that same list if you have something newer.

Edit: (Note newly attached)

Sub ADOToAccess()
'By personal preference, I don't like short abbreviations for variable names
'because they can be a pain a year later when you can't remember why I chose them
'and they make collaboration more difficult since everyone uses their own
'see http://www.xoc.net/standards/default.asp for more naming stuff
Const strcADOConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
Dim cnnConnection As ADODB.Connection
Dim rstPersons As ADODB.Recordset
Dim lngRow As Long
Dim strPathToMDB As String
Dim strSQL As String
'I stuck your files in the same directory on my machine, so this next line
'was used just for me
strPathToMDB = Workbooks("EXCELtoACCESS.xls").Path & _
"\Austens.mdb"
'Use this next line instead for yours
'note that I defined a name for this cell
'strPathToMDB = Worksheets("Data").Range("PathToMDB").Value
strSQL = "SELECT per.PersonID, per.FirstName, per.LastName " _
& "FROM tblPersons as per"
Set cnnConnection = New ADODB.Connection
With cnnConnection
.ConnectionString = strcADOConnString
.Open strPathToMDB
End With
Set rstPersons = New ADODB.Recordset
rstPersons.Open strSQL, cnnConnection, adOpenKeyset, adLockOptimistic

'Can loop in lots of ways, depending on your circumstances
'this way isn't much good unless you already have the ranged defined
'but I just wanted to show you how to use ADO
For lngRow = 4 To 9
With rstPersons
.AddNew
.Fields!FirstName = Range("A" & lngRow).Value
.Fields!LastName = Range("B" & lngRow).Value
.Update
End With
Next lngRow

rstPersons.Close
cnnConnection.Close
Set rstPersons = Nothing
Set cnnConnection = Nothing
End Sub

I'm too lazy to do a DAO example even if it's still what I always use. If someone wants an example though, I'll make one.