Saving records through ODBC connection from Access 2007 to SQL Server 2005

[ncdata]

Hello. I have a database that is being created in Access 2007. I have a table in a SQL server database which I am connecting to via ODBC connection. There is a form "New Scope" that has the control source set to the dbo.SCOPE table linked through the ODBC connection. Within the SCOPE table there are a number of fields that are filled in and then a save command button will be selected with the goal of saving the form data to SQL server. I have included my code below. I am fairly new to coding in access 2007 but have done some coding in 2003 a few years ago.

Private Sub savescope_Click()
Dim dbs As DAO.Database
Dim strSQL As String
Dim Status As String
Dim estimator As String
Dim projectname As String
Dim product As String
Dim category As String
Dim clientname As String
Dim newbizclientname As String
Dim versioncomment As String
Dim SDate As Date
Dim ppmc As Integer
Dim version As Integer
Dim duration As Integer
Dim standardhours As Integer
Dim particpants As Integer
Dim deconversion As String
Dim plans As Integer
Dim edmslink As String
'Validate All Fields
If IsNull(ScopeID.Value) Then
Me.ScopeID.BackColor = vbRed
Me.ScopeID.SetFocus
ElseIf IsNull(ScopeDate.Value) Then
Me.ScopeDate.BackColor = vbRed
Me.ScopeDate.SetFocus
ElseIf IsNull(ScopeStatus.Value) Then
Me.ScopeStatus.BackColor = vbRed
Me.ScopeStatus.SetFocus
ElseIf (ScopeEstimatorName.Value) Then
Me.ScopeEstimatorName.BackColor = vbRed
Me.ScopeEstimatorName.SetFocus
ElseIf (List40.Value) Then
Me.List40.BackColor = vbRed
Me.List40.SetFocus
ElseIf (ScopeProjectPPMCNumber.Value) Then
Me.ScopeProjectPPMCNumber.BackColor = vbRed
Me.ScopeProjectPPMCNumber.SetFocus
ElseIf (ScopeProjectName.Value) Then
Me.ScopeProjectName.BackColor = vbRed
Me.ScopeProjectName.SetFocus
ElseIf (ScopeClientName.Value) Then
Me.ScopeClientName.BackColor = vbRed
Me.ScopeClientName.SetFocus
ElseIf (ScopeNewBusinessClientName.Value) Then
Me.ScopeNewBusinessClientName.BackColor = vbRed
Me.ScopeNewBusinessClientName.SetFocus
ElseIf (ScopeCategory.Value) Then
Me.ScopeCategory.BackColor = vbRed
Me.ScopeCategory.SetFocus
ElseIf (ScopeStandardHours.Value) Then
Me.ScopeStandardHours.BackColor = vbRed
Me.ScopeStandardHours.SetFocus
ElseIf (ScopeVersion.Value) Then
Me.ScopeVersion.BackColor = vbRed
Me.ScopeVersion.SetFocus
ElseIf (ScopeVersionComments.Value) Then
Me.ScopeVersionComments.BackColor = vbRed
Me.ScopeVersionComments.SetFocus
ElseIf (ScopeProjectPPMCNumber.Value) Then
Me.ScopeProjectDuration.BackColor = vbRed
Me.ScopeProjectDuration.SetFocus
ElseIf (deconv.Value) Then
Me.deconv.BackColor = vbRed
Me.deconv.SetFocus
ElseIf (ScopeImpactedParticipants.Value) Then
Me.ScopeImpactedParticipants.BackColor = vbRed
Me.ScopeImpactedParticipants.SetFocus
ElseIf (ScopeImpactedPlans.Value) Then
Me.ScopeImpactedPlans.BackColor = vbRed
Me.ScopeImpactedPlans.SetFocus
ElseIf (ScopeEDMSLink.Value) Then
Me.ScopeEDMSLink.BackColor = vbRed
Me.ScopeEDMSLink.SetFocus
MsgBox (" Scope Form contains missing information. Please verify all fields in red.")
End If


Status = Me.ScopeStatus.Value
estimator = Me.ScopeEstimatorName.Value
projectname = Me.ScopeProjectName.Value
product = Me.List40.Value
category = Me.ScopeCategory.Value
clientname = Me.ScopeClientName.Value
newbizclientname = Me.ScopeNewBusinessClientName.Value
versioncomments = Me.ScopeVersionComments.Value
ScopeDate = Me.ScopeDate.Value
ppmc = Me.ScopeProjectPPMCNumber.Value
version = Me.ScopeVersion.Value
duration = Me.ScopeProjectDuration.Value
standardhours = Me.ScopeStandardHours.Value
participants = Me.ScopeImpactedParticipants.Value
deconversion = Me.deconv.Value
plans = Me.ScopeImpactedPlans.Value
edmslink = Me.ScopeEDMSLink.Value

Set dbs = CurrentDb()
DRIVER=SQL Server;SERVER=PWIRTPDBJD1W3;DATABASE=DB_Data_Conversion_Scoping_Tool;Trusted_Connection=Yes;TABLE= dbo.SCOPE
strSQL = "INSERT INTO dbo.SCOPE (Status,estimator,projectname,product,category,clientname,newbizclientname, versioncomments, ScopeDate,ppmc,version,duration,standardhours,participants,deconversion,planns,edmslink)VALUES (&'Status'&,&'estimator'&,&'projectname'&,&'product'&,&'category'&,&'clientname'&,&'newbizclientname'&,&'versioncomments'&,&'Scopedate'&,&'ppmc'&,&'version'&,&'duration'&,&'standardhours'&,&'participants'&,&'deconversion'&,&'plans'&,&'edmslink'&"
CurrentDb.Execute strSQL, dbFailonError
MsgBox ("Scope Information Closed")

Err_test:
MsgBox "Error:'&Err.Description"


End Sub

 

[mdlueck]

And your question is... (I did not see one.)

The way I code validation is that I validate all fields each time through the validation such that all bad fields are marked bad, and if one that was previously bad is fixed then that field is reset to white color. I maintain a global good/bad flag, and at the end of field validation if that is set to bad then I exit the code / do not commit the changes.

 

[ncdata]

I apologize. I meant to ask is this code correct and if not what do I need to do to make where once the command button is selected it saves to the SQL server backend table.

 

[mdlueck]

I apologize. I meant to ask is this code correct

I suppose it might work. I do not deal with editing bound forms, so someone else will have to comment on that detail.

 

[ncdata]

Do you think if I made it a unbound form it would work?

 

[pbaldy]

The insert SQL will not work. You need to concatenate the variables into the string. Here's a tutorial:

http://www.baldyweb.com/BuildSQL.htm

Also, your code runs down to the SQL whether the data passes validation or not. By the way, if the form is bound, why do you need the SQL at all?

 

[mdlueck]

You need to concatenate the variables into the string. Here's a tutorial:

http://www.baldyweb.com/BuildSQL.htm

Or use an adoCMD / adoParameters objects:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

By the way, if the form is bound, why do you need the SQL at all?

Ja, ma ei tea...