Mavila
01-16-2013, 09:12 PM
OK, I have another thread going on another topic, but it's related to the same file as this one. I've been able to make some inserts of data into the tables, or so I thought, but no data is there in the tables. The code seems to run as it should and insert the data (if I step through it), but nothing is in the table. No data at all. Whic is weird for a couple reasons. the recordcount property shows one record (so does the table at the bottom), but there is no data and the fields all appear to be null.
The second thing that's weird is that the database file's size is 2.32 MB, but there's no data at all in either of the two tables. There are about 80 fields and many area text fields - maybe that's it?
Anyway, here's the code I'm using to insert a record, but as I say it doesn't enter any data despite the fact that it doesn't throw any errors and seems to work just right:
Dim Cn As ADODB.Connection '* Connection String
Dim oCm As ADODB.Command '* Command Object
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBPath & "';Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn
oCm.CommandText = "INSERT INTO [Employers] ([ERNm], [UBI], [LI], [ERAdd1], [ERAdd2], [ERCity], [ERState], [ERZip], [EREmailAdds], [RetroEm]) " _
& "VALUES ('" & Trim(txtERNm) & "','" & Trim(txtERAdd1) & "','" & Trim(txtERAdd2) & "','" & Trim(txtERCity) & "','" & Trim(txtERState) & "', " _
& "'" & Trim(txtERZip) & "','" & Trim(txtERUBI) & "','" & Trim(txtLIAcctNum) & "','" & Trim(txtEREmails) & "','" & Trim(txtRetroEmail) & "');"
oCm.Execute
If Cn.State <> adStateClosed Then
Cn.Close
End If
Application.StatusBar = False
If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing
The second thing that's weird is that the database file's size is 2.32 MB, but there's no data at all in either of the two tables. There are about 80 fields and many area text fields - maybe that's it?
Anyway, here's the code I'm using to insert a record, but as I say it doesn't enter any data despite the fact that it doesn't throw any errors and seems to work just right:
Dim Cn As ADODB.Connection '* Connection String
Dim oCm As ADODB.Command '* Command Object
Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBPath & "';Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open
Set oCm = New ADODB.Command
oCm.ActiveConnection = Cn
oCm.CommandText = "INSERT INTO [Employers] ([ERNm], [UBI], [LI], [ERAdd1], [ERAdd2], [ERCity], [ERState], [ERZip], [EREmailAdds], [RetroEm]) " _
& "VALUES ('" & Trim(txtERNm) & "','" & Trim(txtERAdd1) & "','" & Trim(txtERAdd2) & "','" & Trim(txtERCity) & "','" & Trim(txtERState) & "', " _
& "'" & Trim(txtERZip) & "','" & Trim(txtERUBI) & "','" & Trim(txtLIAcctNum) & "','" & Trim(txtEREmails) & "','" & Trim(txtRetroEmail) & "');"
oCm.Execute
If Cn.State <> adStateClosed Then
Cn.Close
End If
Application.StatusBar = False
If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing