If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Opening Access database from Excel
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 |
#3
|
|||
|
|||
Opening Access database from Excel
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 |
#4
|
|||
|
|||
Opening Access database from Excel
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 |
#5
|
|||
|
|||
Opening Access database from Excel
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? -- |
#6
|
|||
|
|||
Opening Access database from Excel
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 |
#7
|
|||
|
|||
Opening Access database from Excel
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 |
#8
|
|||
|
|||
Opening Access database from Excel
"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... -- |
Thread Tools | |
Display Modes | |
|
|