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

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/

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/

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

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/

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/