By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,707 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,231 IT Pros & Developers. It's quick & easy.

Getting data from Access DB while in excel.

P: n/a
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?

Sep 6 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Sep 7, 5:45 am, 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?
Off the cuff, I would say you'd want to create an ODBC DSN name on the
machine (see Administrative Tools Data Sources (ODBC) on WXP and
specify the filename, workgroup file, credentials. Then using the
external database query tools connect via that DSN.

Sep 6 '07 #2

P: n/a
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

Sep 7 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.