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. |
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. |
All times are GMT +1. The time now is 12:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com