Excel/Access ODBC
Hi All,
I have setup an Access database with a bunch of stuff in it. I have then created a whole bunch of excel spreadsheets which view the data using MS Query. I have now secured the Access database and Excel can no longer access the database, it says 'no permissions' and then throws up a login form. I have typed in the username and password without success. I have also tried to recreate the MS Query from scratch and cannot get into the database. What am i doing wrong? Thanks Emma |
Excel/Access ODBC
Have you considered dumping that useless method? Take a look at this:
http://support.microsoft.com/default...b;EN-US;246335 Using ADO to trsfr data to Excel from Access (or many other sources) is really a piece of cake. I will include a snippet of code below that is an example of dealing with the Access password. I should mention that they say you cannot use it to open Excel from Access if the workbook has a password, even if you pass a correct password to the code. Anyway, that is not what you are asking for. Read the article and if you like it, it works from Excel, pulling data from Access and controlled by Excel. ' Set the string to the path of the database strDB = "H:\Dir1\Dir2\Dir3\Dir4\Mydatabase.mdb;" & "Jet OLEDB:Database Password=mycatsname" ' Open connection to the database cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDB & ";" ' Open recordset from table in db rst.Open "SELECT Social,[Unit Type], val(format(Units,""0.00""))AS Units,val(format(Wages,""0.00""))AS Wages,SubAcct AS Sub " & _ " FROM [tablename] ;", cnt '& _ '"GROUP BY [Acct],SubAcct,Social,[Unit Type];" Remember, the above is just a snippet that shows about dealing with the Access password. More help available if you need it. HTH -- RMC,CPA "Emma Hope" wrote in message ... Hi All, I have setup an Access database with a bunch of stuff in it. I have then created a whole bunch of excel spreadsheets which view the data using MS Query. I have now secured the Access database and Excel can no longer access the database, it says 'no permissions' and then throws up a login form. I have typed in the username and password without success. I have also tried to recreate the MS Query from scratch and cannot get into the database. What am i doing wrong? Thanks Emma |
All times are GMT +1. The time now is 09:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com