On Sep 6, 2:45 pm, radink <radi...@gmail.comwrote:
I have an established secured access database. I would like to read
fields in the Access DB while im in excel via the external database
query tools. However im getting an error about not having permission
to the db. I enter my crudentials and I still get the error. Any ideas
on how to go about this?
Here's a shot in the dark. I noticed that getting Access data from
Excel requires some delicate handling of the Recordset object because
it doesn't know what CurrentDb means. After setting a reference in
Excel to the DAO Object library I tried:
Sub Macro1()
Dim MyDB As Database
Dim MyRS As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM MyTable;"
Set MyDB = Workspaces(0).OpenDatabase("C:\Test.mdb")
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
MyRS.MoveFirst
MsgBox (MyRS("MyField"))
MyRS.Close
Set MyRS = Nothing
Set MyDB = Nothing
End Sub
Note that I haven't addressed the security part. The OpenDatabase
method has a 'connect' variant "(String subtype) that specifies
various connection information, including passwords."
I'm not sure what the external database query tools are. Perhaps you
can teach me about them?
I don't know if anyone will find the code useful or not. Note that
Personal.xls will probably need to be used if the macro needs to work
within all spreadsheets. I think that Personal.xls needs to be
unhidden, macro edited, saved and then hidden again.
James A. Fortune
CD********@FortuneJames.com