Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Not populating in Access database from excel form
Hi All, I am having an issue with my database. I connected my excel form to access database using DAO which is not populating the access database. Here is my code to connect to the access database. Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB") Set rs = db.OpenRecordset("Demo_Table", dbOpenTable) r = 3 Do While Len(Range("A" & r).Formula) 0 With rs .AddNew .Fields("Officer Name") = Range("A" & r).Value .Fields("Officer Phone #") = Range("D" & r).Value .Fields("Contact Person Name") = Range("E" & r).Value .Fields("Contact Person Phone #") = Range("F" & r).Value .Fields("Address") = Range("K" & r).Value .Fields("City") = Range("L" & r).Value .Fields("State") = Range("M" & r).Value .Fields("Zip Code") = Range("N" & r).Value .Fields("Special Instructions/Comments") = Range("P" & r).Value .Update End With r = r + 1 Loop rs.Close Set rs = Nothing db.Close Set db = Nothing This is my code to connect to the access database, Its not displaying any error. Once I click "Submit" button on excel user form it just does nothing. It does populate in excel sheet that I have, But not the Access database. PLEASE HELP. Thanks in Advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Not populating in Access database from excel form
Im not sure with DAO but this will work for ADODB Option Explicit Private Sub saveDataToAccess() 'Needs reference the Axtive X Library 2.0 or higher Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, strConn Dim r As Integer r = 6 'access database 2003 or less 'C:\PathToYourMdb\Ilsa.mdb (Change) strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _ & "C:\PathToYourMdb\Ilsa.mdb;Persist Security Info=False" 'Use for Access (Ace No Security) 'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;" _ '& "Persist Security Info=False;" 'Use for Access (Ace With Security) 'strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;" _ '& "Jet OLEDB:Database Password=MyDbPassword;" 'sSQL = Name Of Your Access table Change to your 'Table Name sSQL = "TableName" Set cnn = New ADODB.Connection Set rs = New ADODB.Recordset cnn.Open strConn rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable 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("Field1") = Range("A" & r).Value .Fields("Field2") = Range("E" & r).Value .Fields("Field3") = Range("F" & r).Value .Fields("Field4") = Range("G" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close cnn.Close End Sub "sam" wrote: Hi All, I am having an issue with my database. I connected my excel form to access database using DAO which is not populating the access database. Here is my code to connect to the access database. Dim db As Database, rs As Recordset, r As Long Set db = OpenDatabase("C:\Documents and Settings\My Documents\DemoDB") Set rs = db.OpenRecordset("Demo_Table", dbOpenTable) r = 3 Do While Len(Range("A" & r).Formula) 0 With rs .AddNew .Fields("Officer Name") = Range("A" & r).Value .Fields("Officer Phone #") = Range("D" & r).Value .Fields("Contact Person Name") = Range("E" & r).Value .Fields("Contact Person Phone #") = Range("F" & r).Value .Fields("Address") = Range("K" & r).Value .Fields("City") = Range("L" & r).Value .Fields("State") = Range("M" & r).Value .Fields("Zip Code") = Range("N" & r).Value .Fields("Special Instructions/Comments") = Range("P" & r).Value .Update End With r = r + 1 Loop rs.Close Set rs = Nothing db.Close Set db = Nothing This is my code to connect to the access database, Its not displaying any error. Once I click "Submit" button on excel user form it just does nothing. It does populate in excel sheet that I have, But not the Access database. PLEASE HELP. Thanks in Advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel form populating in access 2002-03 but not 2007 | Excel Programming | |||
connect excel user form to access database | Excel Programming | |||
auto populate access database from an excel form | Excel Programming | |||
Excel template as form for data entry in Access database | Excel Discussion (Misc queries) | |||
Error while populating a combobox from Access database | Excel Programming |