I have an excel sheet with some data on sheet2 and I need to export
this to an access database secured via workgroups security. The code I
have works fine with database without security workgroup.
****************************************************************
Sub EXPORT()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\Anubhav\Desktop\Desktop
\Mydb.mdb","USERID","PASSWORD"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 2 ' the start row in the worksheet
'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
.Fields("Status") = Sheets("Sheet2").Range("B" & r).Value
.Fields("Type") = Sheets("Sheet2").Range("C" & r).Value
.Update ' stores the new record
End With
' r = r + 1 ' next row
'Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,000 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
