project(.adp) file & SQL Server -- VBA connection?

  • Thread starter Thread starter Andrea
  • Start date Start date
A

Andrea

Hi there,

I have an MS Access project that connects me to a SQL
Server database. My forms are all working fine, but I've
been trying to figure out how to get at the data
programmatically, using VBA.

I'm assuming that since the project is already connected
to the database, that I don't have to connect in my code.
Is that correct? If not, how do I connect? ODBC?

Any simple examples of a connection and creation of a
recordset would be greatly appreciated!

Thanks, Andrea
 
Any simple examples of a connection and creation of a
recordset would be greatly appreciated!
Click to expand...

Here's one with some validation stuff thrown in...

' *** Code Start ***
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdCancel_Click
Dim rs As ADODB.Recordset
Dim sql As String

If (IsNull(Me.txtData) Or IsNull(Me.txtValue)) Then
MsgBox "Please provide a numerical value for both " & _
"'Period' and 'Value' textboxes.", _
vbExclamation Or vbOKOnly, "Validation Error"
Exit Sub
End If

If (Not IsNumeric(Me.txtData) Or Not IsNumeric(Me.txtValue)) Then
MsgBox "Please provide a numerical value for both 'Period' " & _
"and 'Value' textboxes.", _
vbExclamation Or vbOKOnly, "Validation Error"
Exit Sub
End If


' first make sure that the year doesn't already exist
If (StrComp("index_data", table, vbTextCompare) = 0) Then
sql = "select period from index_data where ref=" & _
Me.txtRef & " and period = " & Me.txtData
ElseIf (StrComp("EmpiricalCurve_Data", table, vbTextCompare) = 0) Then
sql = "select x from EmpiricalCurve_Data where ref=" & _
Me.txtRef & " and x = " & Me.txtData
ElseIf (StrComp("ParameterisedCurve_Data", table, vbTextCompare) = 0)
Then
sql = "select parameter_no from ParameterisedCurve_Data" & _
" where ref=" & Me.txtRef & " and parameter_no = " & Me.txtData
End If

If (Len(sql) > 0) Then
Set rs = New ADODB.Recordset
rs.Open sql, CurrentProject.AccessConnection, _
adOpenForwardOnly, adLockReadOnly
If (rs.RecordCount > 0) Then
MsgBox "A value for the specified Period '" & _
Me.txtData & "' already exists." & vbCrLf & _
"Please try again.", vbExclamation Or vbOKOnly, _
"Validation Error"
rs.Close
Exit Sub
End If
rs.Close
Else
MsgBox "Unknown table " & table & ". Please try again.", _
vbExclamation Or vbOKOnly, "Unknown table Error"
Exit Sub
End If

' insert into the table
If (StrComp("index_data", table, vbTextCompare) = 0) Then
sql = "insert into index_data (ref, period, [index]) values " & _
"(" & Me.txtRef & ", " & Me.txtData & ", " & Me.txtValue & ")"
CurrentProject.AccessConnection.Execute sql, , adCmdText
MsgBox "Period successfully added.", _
vbInformation Or vbOKOnly, "Success."

ElseIf (StrComp("EmpiricalCurve_Data", table, vbTextCompare) = 0) Then
sql = "insert into EmpiricalCurve_Data (ref, x, lev_x) values " & _
"(" & Me.txtRef & ", " & Me.txtData & ", " & Me.txtValue & ")"
CurrentProject.AccessConnection.Execute sql, , adCmdText
MsgBox "Data successfully added.", _
vbInformation Or vbOKOnly, "Success."

ElseIf (StrComp("ParameterisedCurve_Data", table, vbTextCompare) = 0)
Then
sql = "insert into ParameterisedCurve_Data " & _
" (ref, parameter_no, parameter) values " & _
"(" & Me.txtRef & ", " & Me.txtData & ", " & Me.txtValue & ")"
CurrentProject.AccessConnection.Execute sql, , adCmdText
MsgBox "Data successfully added.", _
vbInformation Or vbOKOnly, "Success."

End If

DoCmd.Close acForm, Me.Name
Exit Sub

Err_cmdCancel_Click:
With Err
MsgBox "Error: " & .Number & vbCrLf & _
.Description, vbCritical Or vbOKOnly, _
"cmdAdd_Click of Form_frmAddDataValuePopup"
End With
End Sub
'*** Code End ***

-- Dev
 
Back
Top