So whoever built this is no longer available to fix?
The button Click event turns off warnings but warnings are never turned back on. Modify like:
Code:
Private Sub cmd_update_Click()
DoCmd.SetWarnings False
ListESY
MsgBox "Table created and data inserted"
DoCmd.SetWarnings True
End Sub
Suggest getData, CreateTable, getdataintotable can each be Sub instead of Function then use Call to run them.
Code is just repeatedly deleting and creating the first file and choking on the createdb procedure. Modify ListESY procedure:
Code:
Public Sub ListESY()
Dim oFile As Object
Dim oFSO As Object
Dim oFolder As Object
Dim oFiles As Object
Dim strFPath As String, strTable As String, strFile As String
strFPath = Application.CurrentProject.Path & "\csv"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(strFPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
strFile = Dir(strFPath & "\" & oFile.Name, vbNormal)
strTable = Left(oFile.Name, Len(oFile.Name) - 4)
Call getData(strFPath, strFile, strTable)
Next
End Sub
Code is naming fields like Valeur1, etc. and the CSV header names are inserted into single field of record. Don't you want to use the CSV headers as field names? The code is only looking for "BET" header. None of the sample files have this header so only 0 is populated into every field. The fourth file has an additional field "Raise 70". One line of code appears to expect 7 fields, not 8.
Modify CreateTable procedure:
Code:
Public Sub CreateTable(RS As ADODB.Recordset, strFile As String)
Dim count As Integer, i As Integer
Dim dbs As DAO.Database, tbl As TableDef, fld As Field
Dim exists As Boolean
Set dbs = CurrentDb
Set tbl = dbs.CreateTableDef(strFile)
Set fld = tbl.CreateField("Flop_Hand", dbText, 255)
tbl.fields.Append fld
count = RS.fields.count
For i = 2 To count - 1
Set fld = tbl.CreateField(RS.fields(i).Name, dbInteger)
tbl.fields.Append fld
NextDim exists As Boolean
exists = doesTableExist(strFile)
If doesTableExist(strFile) Then
DoCmd.DeleteObject acTable, strFile
End If
dbs.TableDefs.Append tbl
dbs.TableDefs.Refresh
DoCmd.SelectObject acTable, strFile, True
End Sub
Modify getdataintotable procedure:
Code:
Public Sub getdataintotable(rs1 As ADODB.Recordset, tablename1 As String)
Dim strquery As String
Dim no2 As Integer
Dim i As Integerrs1.MoveFirst
Do While Not rs1.EOF
strquery = "Insert into [" & tablename1 & "] values ('" & rs1.fields(0) & "_" & rs1.fields(1) & "',"
For i = 2 To rs1.fields.count - 1
no2 = InStr(rs1.fields(i).Name, "BET") Or InStr(rs1.fields(i).Name, "RAISE") Or InStr(rs1.fields(i).Name, "CALL")
If no2 > 0 Then
strquery = strquery & rs1.fields(i) & ","
Else
strquery = strquery & "0,"
End If
Next
strquery = Left(strquery, Len(strquery) - 1) & ")"
CurrentDb.Execute strquery
rs1.MoveNext
Loop
'createdb (tablename1)
End Sub
I simply commented out the call to createdb in getdataintotable procedure for now. Do you want to create an Access file for each table?
Module header should have following two lines and the public variable fields is not needed in the header if suggested edits are implemented:
Option Compare Database
Option Explicit
Be sure to run Debug>Compile after code edits. And also Compact & Repair periodically.