Execute "insert into crimedata(report Year, state, school, campus, data Label, data Value)values(" _ & report Year & ", " _ & state & ", " _ & school & ", " _ & campus & ", " _ & data Label & ", " _ & data Value & ")" cn.
If you could do that and include a sample of the actual sql generated that would help a
I'm struggling with some VBA code and the Before Save methodology. Number) = 91) End Function This will flag every field that has an error in yellow and add a comment as to what the issue is you could also determine a way to tell the user exactly where the errors are using v.uniq_keys which returns a collection of cell address' that fail validation of presence. Am I right in assuming the above code goes into the ' This Workbook and the two class modules go into their own 'modules'? These were designed in 2007 and work perfectly with excel and access 2007.
I've been all over the forums but can't locate the answer I need, so would love some help please. On saving I need the code to look at Column H (named Claim USD) of a 'Table' (named Claims) for a number value and then if any of the cells has a value to then look at Column I (named Claim Date) and make sure there is a date in there. Clear Comments End Sub Public Function has_comments(c_cell As String) As Boolean On Error Resume Next Sheet1. If you would like me to explain how it works I can but the read me on github explains a lot about it as well.
I have already data validated column I to only accept date entries. Private Sub Workbook_Before Save(By Val Save As UI As Boolean, Cancel As Boolean) Dim rsave As Range Dim cell As Range Set rsave = Sheet2. Private Sub Workbook_Before Save(By Val Save As UI As Boolean, Cancel As Boolean) Dim rsave As Range Dim cell As Range Set rsave = Sheet2. Value = "" Then Dim missdata missdata = Msg Box("Missing Data - Enter the Date for Work Book to Save", vb OKOnly, "Missing Data") Cancel = True cell.
The premise behind this program is to take several different excel sheets and input them in to an access database.
Everything is working until my execute statement which produces the following error: Sub update Data() Dim cn As New ADODB.
Connection Dim row As Integer Dim col As Integer Dim src Row As Integer Dim src Col As Integer Dim filename As String Dim target Workbook As Workbook Dim target Sheet As Worksheet Dim report Year As String Dim state As String Dim school As String Dim campus As String Dim data Label As String Dim data Value As Long On Error Resume Next ' these lines should allow you to connect.
Depending on your version of windows, only one will execute without error.
Path & "\data\*.xls") 'Loops through all the file names in the data directory Do Until filename = "" Set source Workbook = Application.
Screen Updating = False filename = Dir(This Workbook. Path & "\data\" & filename) Set source Sheet = source Workbook.
row = 6 col = 3 src Row = 1 src Col = 1 Application.
Path & "\university Crime.mdb" & ";" On Error Go To 0 'Starting here!