Excel Code to Read / Write to SQL

[nevets2001uk]

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...

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...

'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)

 

[PHV]

strFilePath should contain a valid DSN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nevets2001uk]

Apolgies for being a bit slow with this. I've tried this

Set dbDODbase = DBEngine.Workspaces(0).Opendatabase("Driver={SQL Server};Server=HMD-DB;Database=DODBase;Uid=DODBASE;Pwd=dodbase;")

which is based on some searching around the web and it didn't work. Any pointers on how is should appear?

Cheers,

Steve G (MCP)

 

[PHV]

Have you tried to press the F1 key while the cursor is inside the OpenDatabase word in your code ?
You may search your local drives for files named DAO*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nevets2001uk]

I've tried using...

Set dbDODbase = DBEngine.Workspaces(0).Opendatabase("", dbDriverNoPrompt, , "odbc;DSN=DODBase;UID=DODBASE;PWD=dodbase")

after looking through the help pages but it now prompts for a DSN. If I go to MachineDataSource and choose my datasource ir prompts for the password. After entering that it seems to proceed. How can I get it to use the datasource automatically?

Also the next line...

Set dbDODbase = DBEngine.Workspaces(0).Opendatabase("", dbDriverNoPrompt, , "odbc;DSN=DODBase;UID=DODBASE;PWD=dodbase")

then displays an 'Invalid Argument' error. Am I going to need to change this as well?

Cheers,


Steve G (MCP)

 

[PHV]

This link may help you:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nevets2001uk]

I think I've figured this all out now. Thanks for the help everyone.

Cheers,

Steve G (MCP)