Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Excel data to Access Security problem
I used Erlandsens Data Consulting'
(http://www.erlandsendata.no/english/...hp?t=envbamain) DOA an ADO examples and successfully imported data using either format into a access table to an unsecured Access 2002 database. I have a client where I am using Access 2003 which uses security wit logins and all, and when I tried to import into that database I get th message unable to access the database. How can I built the security settings into the excel macro? Any help would be greatly appreciated Anneli -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Excel data to Access Security problem
http://support.microsoft.com/default...54&Product=ado
HOWTO: Open a Secured Access Database in ADO Through OLE DB -- Regards, Tom Ogilvy "Annelie" wrote in message ... I used Erlandsens Data Consulting's (http://www.erlandsendata.no/english/...hp?t=envbamain) DOA and ADO examples and successfully imported data using either format into an access table to an unsecured Access 2002 database. I have a client where I am using Access 2003 which uses security with logins and all, and when I tried to import into that database I get the message unable to access the database. How can I built the security settings into the excel macro? Any help would be greatly appreciated Annelie --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Excel data to Access Security problem
Here is another article:
http://support.microsoft.com/default...22&Product=ado HOWTO: Use ADO to Refresh/Create Linked Table for Password Secured Jet 4.0 Database -- Regards, Tom Ogilvy "Annelie" wrote in message ... I used Erlandsens Data Consulting's (http://www.erlandsendata.no/english/...hp?t=envbamain) DOA and ADO examples and successfully imported data using either format into an access table to an unsecured Access 2002 database. I have a client where I am using Access 2003 which uses security with logins and all, and when I tried to import into that database I get the message unable to access the database. How can I built the security settings into the excel macro? Any help would be greatly appreciated Annelie --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Excel data to Access Security problem
Sub MacroTime()
'replace path with the path and name of your security.mdw' DBEngine.SystemDB = "c:\DATA\_PCI\ACCESS\security.mdw" DBEngine.DefaultUser = "YourUserName" Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase("YourDatabaseName.mdb") Set rs = db.OpenRecordset("YourTable", dbOpenTable) Dim r As Long ' open the database r = 2 ' the start row in the worksheet assuming there are headers in excel. Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record 'Rename to your access field Names ' .Fields("Field1") = Range("A" & r).Value .Fields("Field2") = Range("B" & r).Value .Fields("Field3") = Range("C" & r).Value .Fields("Field4") = Range("D" & r).Value .Fields("Field5") = Range("E" & r).Value .Fields("Field6") = Range("F" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub Please note that I originally took the code from: http://www.erlandsendata.no/english/...hp?t=envbamain which worked fine for an unsecured database. By trial and error I adde the lines to upen a secured database by taking instructions from http://support.microsoft.com/default...p#_Toc49329970 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Excel data to Access Security problem
http://support.microsoft.com/default...#_Toc493299711
looks more like what you used. And it is pretty much a straight lift from there. -- Regards, Tom Ogilvy "Annelie" wrote in message ... Sub MacroTime() 'replace path with the path and name of your security.mdw' DBEngine.SystemDB = "c:\DATA\_PCI\ACCESS\security.mdw" DBEngine.DefaultUser = "YourUserName" Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase("YourDatabaseName.mdb") Set rs = db.OpenRecordset("YourTable", dbOpenTable) Dim r As Long ' open the database r = 2 ' the start row in the worksheet assuming there are headers in excel. Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs AddNew ' create a new record ' add values to each field in the record 'Rename to your access field Names ' Fields("Field1") = Range("A" & r).Value Fields("Field2") = Range("B" & r).Value Fields("Field3") = Range("C" & r).Value Fields("Field4") = Range("D" & r).Value Fields("Field5") = Range("E" & r).Value Fields("Field6") = Range("F" & r).Value ' add more fields if necessary... Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub Please note that I originally took the code from: http://www.erlandsendata.no/english/...hp?t=envbamain which worked fine for an unsecured database. By trial and error I added the lines to upen a secured database by taking instructions from http://support.microsoft.com/default...#_Toc493299703 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Excel data to Access Security problem
Your are right, that is what I used. Sorry for getting that mixed up.
Annelie "Tom Ogilvy" wrote in message ... http://support.microsoft.com/default...#_Toc493299711 looks more like what you used. And it is pretty much a straight lift from there. -- Regards, Tom Ogilvy "Annelie" wrote in message ... Sub MacroTime() 'replace path with the path and name of your security.mdw' DBEngine.SystemDB = "c:\DATA\_PCI\ACCESS\security.mdw" DBEngine.DefaultUser = "YourUserName" Set ws = DBEngine.Workspaces(0) Set db = ws.OpenDatabase("YourDatabaseName.mdb") Set rs = db.OpenRecordset("YourTable", dbOpenTable) Dim r As Long ' open the database r = 2 ' the start row in the worksheet assuming there are headers in excel. Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs AddNew ' create a new record ' add values to each field in the record 'Rename to your access field Names ' Fields("Field1") = Range("A" & r).Value Fields("Field2") = Range("B" & r).Value Fields("Field3") = Range("C" & r).Value Fields("Field4") = Range("D" & r).Value Fields("Field5") = Range("E" & r).Value Fields("Field6") = Range("F" & r).Value ' add more fields if necessary... Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub Please note that I originally took the code from: http://www.erlandsendata.no/english/...hp?t=envbamain which worked fine for an unsecured database. By trial and error I added the lines to upen a secured database by taking instructions from http://support.microsoft.com/default...#_Toc493299703 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Export from Access to Excel | Excel Discussion (Misc queries) | |||
How can I Export data from to Access to an Excel Template | Excel Discussion (Misc queries) | |||
Export memo field in Access to Excel - data gets cut off | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Export Excel Data to Access With a Macro!!! | Excel Discussion (Misc queries) |