nevets2001uk
IS-IT--Management
I have just updated an access database to SQL only to find out they use a spreadsheet to write some data to the database. I've looked through the code and can't quite work out how I should change it in order to connect to the SQL database via ODBC.
Here's an example of the code...
and a snippet from the add data function...
Any pointers to push me in the right direction would be appreciated. I'd like to change as little of the code as possible. Hopefully just the connection sections?
Cheers,
Steve G (MCP)
Here's an example of the code...
Code:
Function CheckJobNo(jobNo As String) As Boolean
'Checks the record being saved does not exist and returns
'a true value if a record does not exist.
Dim intMsg As Integer
Dim dbDODbase As Database
Dim rstDataToSave As Recordset
'Create the dbDODbase object by opening a database
Set dbDODbase = DBEngine.Workspaces(0).Opendatabase(strFilePath)
'Create the rstTable object from the open database
Set rstDataToSave = dbDODbase.OpenRecordset("tblDocs", dbOpenTable)
'Check to see whether the pump number exists
With rstDataToSave
.Index = "PumpNo"
.Seek "=", jobNo
End With
'Assign a value to CheckJobNo
If rstDataToSave.NoMatch = True Then
CheckJobNo = True
Else
CheckJobNo = False
End If
'Close the table
rstDataToSave.Close
'Close the database
dbDODbase.Close
End Function
and a snippet from the add data function...
Code:
'Check to see that the selected record does not exist
If CheckJobNo(strJobno) = False Then
intResponse = MsgBox(strJobno & " already exists in the database. Please select another number.", _
, "Existing Pump Number")
Exit Sub
End If
'************DAO***************
'The database must be opened whilst referring to it within the object heirarchy
Set dbDODbase = DBEngine.Workspaces(0).Opendatabase(strFilePath)
'Create the rstDataToSave object from the open database
Set rstDataToSave = dbDODbase.OpenRecordset("tblDocs", dbOpenTable)
'For intCtr = 0 To 31
'Debug.Print rstDataToSave.Fields(intCtr).Name & "(" & intCtr & ")"
'Next intCtr
'Get data from the spreadsheet and store it in the DataToSave array.
'Looop across the cell range to collect data
intRowNo = ActiveCell.Row
For intCtr = 1 To 27
dataToSave(intCtr) = Cells(intRowNo, intCtr).Value
Next intCtr
'For intCtr = 1 To 27
'Debug.Print dataToSave(intCtr) & "(" & intCtr & ")"
'Next intCtr
'Add a new record to the table
rstDataToSave.AddNew
'TblDocs.PumpNo
rstDataToSave.Fields(0) = dataToSave(1)
'TblDocs.DocsID
'rstDataToSave.Fields(1)
'TblDocs.ContID
'rstDataTosave.Fields(2)
'TblDocs.KitDate
If IsDate(dataToSave(16)) Then
rstDataToSave.Fields(3) = dataToSave(16)
End If
Any pointers to push me in the right direction would be appreciated. I'd like to change as little of the code as possible. Hopefully just the connection sections?
Cheers,
Steve G (MCP)