Export Excel data to Access Security problem

  • Thread starter Thread starter Annelie
  • Start date Start date
A

Annelie

I used Erlandsens Data Consulting'
(http://www.erlandsendata.no/english/index.php?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
 
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/index.php?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.aspx?scid=/support/access/content/secfaq.asp#_Toc49329970
 
Your are right, that is what I used. Sorry for getting that mixed up.
Annelie

Tom Ogilvy said:
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299711

looks more like what you used. And it is pretty much a straight lift from
there.

--
Regards,
Tom Ogilvy

Annelie said:
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/index.php?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
Click to expand...
http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp#_Toc493299703
Click to expand...
 
Back
Top