Opening Access database from Excel

I have an Access database that's secured with a .MDW. I can't find a way to
reference the MDW file when I try to run a query via the "Get External Data"
option. Any ideas?

Tim Poirier wrote:
I have an Access database that's secured with a .MDW. I can't find a way to
reference the MDW file when I try to run a query via the "Get External Data"
option. Any ideas?


Create an ODBC data source.
Details depend on your Office version.
You can create an ODBC data source via Control Panel or when attempting to
connect to a data source via Get External Data / New Database Query. I will
describe the latter.

In the databases dialog select New Data Source and click OK.
In the Create New Data Source dialog enter a descriptive name; in the driver
dropdown select an appropriate Microsoft Access driver; click the Connect
button.
In the ODBC Microsoft Access Setup dialog click the Select button and browse
to your database and click OK (you can select Exclusive or ReadOnly if
relevant); select the Database radio button in the lower frame and then click
the System Database button and browse to your MDW file and click OK; then
click the Advanced button and specify user name (and password if you wish not
to be prompted for it) and click OK 4 times to get back to the query wizard.

Next time you want to use the same database on this machine you should be able
to select it from the first dialog using the descriptive name you entered
above.



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I have the same issue and this solves it except for some access tables using
ODBC link. When I try to
import the access table I get an ODBC failure. Would appreciate any advice.

Thank you
Tim

"Bill Manville" wrote in message
...
Tim Poirier wrote:
I have an Access database that's secured with a .MDW. I can't find a way
to
reference the MDW file when I try to run a query via the "Get External
Data"
option. Any ideas?


Create an ODBC data source.
Details depend on your Office version.
You can create an ODBC data source via Control Panel or when attempting to
connect to a data source via Get External Data / New Database Query. I
will
describe the latter.

In the databases dialog select New Data Source and click OK.
In the Create New Data Source dialog enter a descriptive name; in the
driver
dropdown select an appropriate Microsoft Access driver; click the Connect
button.
In the ODBC Microsoft Access Setup dialog click the Select button and
browse
to your database and click OK (you can select Exclusive or ReadOnly if
relevant); select the Database radio button in the lower frame and then
click
the System Database button and browse to your MDW file and click OK; then
click the Advanced button and specify user name (and password if you wish
not
to be prompted for it) and click OK 4 times to get back to the query
wizard.

Next time you want to use the same database on this machine you should be
able
to select it from the first dialog using the descriptive name you entered
above.



Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Tim wrote:
I have the same issue and this solves it except for some access tables using
ODBC link. When I try to
import the access table I get an ODBC failure. Would appreciate any advice.


Sorry, not enough clues in your message for me to determine what the problem
is.

What version of Office?
How are you importing the table?
What exactly is the message you get?
What field types are in the table?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill Manville wrote in message ...

What version of Office?
How are you importing the table?
What exactly is the message you get?
What field types are in the table?

And do the linked tables require passwords?

--

Bill,

I'm using office 2003. I have an odbc connection using the microsoft access
setup dialog box . Since the access database
is part of a workgroup, I also used system database dialog box to reference
the system.mdw file. In the odbc set advance
option I entered the workgroup username and password.

Using excel, I tried to import external data using the odbc connection. I
am able to import access tables that are not linked, however
I cannot import linked tables that require a password. The error message
reads - [Microsoft][ODBC Microsoft Access Driver] ODBC
connection to 'account' failed. The 'account' is the linked table account
name. The table contains text and numeric fields.

Would appreciate any help.

Thank you,
Tim

"Bill Manville" wrote in message
...
Tim wrote:
I have the same issue and this solves it except for some access tables
using
ODBC link. When I try to
import the access table I get an ODBC failure. Would appreciate any
advice.


Sorry, not enough clues in your message for me to determine what the
problem
is.

What version of Office?
How are you importing the table?
What exactly is the message you get?
What field types are in the table?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Jim wrote:
I cannot import linked tables that require a password.


Sorry, I don't immediately know the answer to this one.
Are the source tables in another Access database or in some other form?
Can you not import directly from the source?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

"Jim" wrote in message .com...

I cannot import linked tables that require a password. The error message
reads - [Microsoft][ODBC Microsoft Access Driver] ODBC
connection to 'account' failed. The 'account' is the linked table account
name.

I suspect you may be suffering from MS Access getting confused with
Jet. Don't worry, it's a common problem, seemingly perpetuated by MS.

Jet is a database engine. MS Access provides GUI tools for managing
Jet databases plus a RAD environment for designing data-centric apps
based primarily, but not limited to, Jet databases. When you hear
'Access database' in this ng chances are they mean a Jet database
(this even seems to be true of the MS Access ngs).

A .mdb file is a Jet database. Tables in the Jet database hold the
database's data, schema, constraints, stored procedures/queries, etc.
For a MS Access application, Jet also holds the forms, reports etc. MS
seem to enjoy blurring the picture of Jet and MS Access e.g.
'Microsoft Access Driver', why not Jet driver?

You do not need MS Access to create, maintain or query a Jet database,
including creating linked tables; you merely need Jet and MDAC, both
of which are available to most Excel users. I'm currently working on a
tool which creates/refreshes linked tables in a Jet database created
on the fly, using 'Microsoft ActiveX Data Objects Extensions for Data
Definition Language and Security' (ADOX for short); I don't use MS
Access.

When you create a linked table using ADOX, there are some useful
properties you can explicitly set e.g. you can specify the connection
string to the provider you are linking to, specify whether to cache
the password, etc.

(This is where my knowledge goes a bit fuzzy because I'm not an MS
Access user.) When you do the same in MS Access, these things are done
implicitly so I don't know if MS Access does things the same way. From
what I read it seems MS Access holds the info, but I can't be sure and
I don't know where it would store e.g. the password (not in the system
tables nor the registry AFAIK).

Here's my suggestion, which you may not like: re-create your links
without using MS Access. This way you will be in control of the
details. You will find these MSDN articles invaluable:

ADO Provider Properties and Settings
http://msdn.microsoft.com/library/de...properties.asp

Creating a Linked Table
http://msdn.microsoft.com/library/de...inkedtable.asp

Creating an Access Database [ see what I mean?!]
http://msdn.microsoft.com/library/de...ssdatabase.asp

FWIW as a test I just now used ADOX to create a linked table in a Jet
database to a table in a password-protected Jet database, then
successfully accessed the linked table using ODBC in MS Query.

Alternatively, you could post your question in one of the MS Access
ngs. However, I suspect you'll run into the opposite problem e.g.
someone who knows a lot about MS Access but whose knowledge starts to
get fuzzy when the talk turns to how Jet does things when MS Access is
not involved. Also bear in mind that MS Access's raison d'etre as a
GUI front-end for Jet is to hide complexity from the user...

--