Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Problems inserting/updating Access

I know this comes up all the time, but I'm not able to find a solution to my issue.

I'm pulling data from one excel sheet and inserting it into an Access Database.

[code]
Function PushDataToDatabase() As Worksheet
Dim dataSht As Worksheet
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DestTable As String

' exports data from the active worksheet to a table in an Access database
DestTable = "IndustryWeights"

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:historic_db_V1.mdb;"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open DestTable, cn, adOpenKeyset, adLockOptimistic, adCmdTable

With rs
'MsgBox (Me.DataID)
.AddNew ' create a new record
' add values to each field in the record
.Fields("InduName") = Me.IndustryName
.Fields("BaseMaxPoints") = Me.baseMaxPoints
.Fields("DataID") = Me.DataID
.Fields("Factor") = Me.Factor
.Fields("Category") = Me.Category
.Fields("SubCategory") = Me.SubCategory
.Fields("WorkSheet") = Me.SourceSheet
.Fields("Matrix") = Me.SourceFilename
.Fields("ProdDate") = Me.wDate
.Fields("CategoryPercent") = Me.CategoryPercent
.Fields("PercentOf2k") = Me.PercentOf2k
.Fields("MaxPoints") = Me.MaxPoints
' add more fields if necessary...
.Save ' stores the new record
End With

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

Set PushDataToDatabase = dataSht

End Function
[/code]

This code runs but never updates the table with the data, with no errors. I know the data from the rest of the program is getting to this function - "MsgBox (Me.DataID)" is showing the correct data.

so I thought I'd take a different approach

[code]Function PushDataToDatabase() As Worksheet
Dim dataSht As Worksheet
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim DestTable As String
Dim strPath As String
Dim Sql As String
Dim cmd As ADODB.Command


' exports data from the active worksheet to a table in an Access database
DestTable = "IndustryWeights"

Set cn = New ADODB.Connection

'just connect to the access database
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:db1.mdb; Persist Security Info=False"

cn.Open

Sql = "Insert into IndustryWeights(InduName, BaseMaxPoints, DataID, Factor, Category, SubCategory, WorkSheet, Matrix, ProdDate, CategoryPercent, PercentOf2k, MaxPoints) VALUES('" & Trim(Me.IndustryName) & "', '" & Trim(Me.baseMaxPoints) & "','" & Trim(Me.DataID) & "','" & Trim(Me.Factor) & "','" & Trim(Me.Category) & "','" & Trim(Me.SubCategory) & "','" & Trim(Me.SourceSheet) & "','" & Trim(Me.SourceFilename) & "','" & Trim(Me.wDate) & "','" & Trim(Me.CategoryPercent) & "','" & Trim(Me.PercentOf2k) & "','" & Trim(Me.MaxPoints) & "')"

'MsgBox (Sql)

cn.Execute (Sql)

cn.Close

cn = ""
Set cn = Nothing



Set PushDataToDatabase = dataSht

End Function
[/code]


this also finishes with no result.

I thought maybe i didnt have Jet installed and it said i have a newer version already installed.

any suggestions?

Comments

  • the_internetthe_internet Member Posts: 2
    turns out the code was fine.

    the data being passed through is ok except for the dates were not actually dates. it was reading from the folder name.
Sign In or Register to comment.