VBA no longer works when linked to SQL server tables

  • Thread starter Thread starter Rhys Davies
  • Start date Start date
R

Rhys Davies

I have linked our existing Access database to SQLand am running the tables
purely in SQL server 2008 having deleted the Acess tables and renamed the
linked tables to what the Access tables used to be called. However when i
try to run some code now it doesnt work. Clicking on the 'submit' button
normally inserts some information from the form screen into 'tblusers' and
then sends an email to an individual letting them know that the submission
has happened etc but now since linking to SQL tables it wont work. If I
comment out the recordset code then the email will be sent but obviously no
data will be inserted into 'tblusers' so it doesnt seem to like the code
concerning rstemp and where I attempt to open a recordset on 'tblusers'.
Does SQL require different code for the insertion to happen?



Any help would be greatly appreciated.



Thanks,



Rhys.



Private Sub submit_Click()
On Error GoTo Err_submit_Click

Me.Expense.Enabled = False

Dim rstemp As Recordset
Dim dls As Date
Dim straddress As String
Dim strbody As String
Dim strsubject As String
strsubject = "End of Month Submission from" & " " & loginname & "" & " " &
"for" & " " & Me.submitmonth & " " & Me.submityear
strbody = "Expenses:" & " " & "£" & Me.expenses & vbCrLf & "Available
Hours:" & " " & Me.totalhours & vbCrLf & "Hours Worked:" & " " &
Me.availablehours & vbCrLf & _
"TOIL @ Start of Month:" & " " & Me.TOIL & vbCrLf & "TOIL Accrued During
Month:" & " " & Me.toilaccduringmonth & vbCrLf & _
"New TOIL Figure:" & " " & Me.toilendofmonth
Set rstemp = CurrentDb.OpenRecordset("tblusers") 'SQL does not like this
section
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname

If rstemp.NoMatch Then
rstemp.AddNew
rstemp!loginname = loginname
rstemp!datelastsubmitted = currentdatesubmit
rstemp!datenextsubmit = datenextsubmit
rstemp!TOIL = toilendofmonth
rstemp.Update
DoCmd.SendObject , , acFormatHTML, "(e-mail address removed)", , , strsubject,
strbody, False, False
Me.currentdatesubmit = ""
Me.datenextsubmit = ""
Me.TOIL = ""
Me.availablehours = ""
Me.toilaccduringmonth = ""
Me.toilendofmonth = ""
Me.expenses = ""
Me.totalhours = ""
Me.datelastsubmitted = ""

Me.currentdatesubmit.Enabled = False
Me.datenextsubmit.Enabled = False
Me.TOIL.Enabled = False
Me.availablehours.Enabled = False
Me.toilaccduringmonth.Enabled = False
Me.toilendofmonth.Enabled = False
Me.expenses.Enabled = False
Me.totalhours.Enabled = False
Me.datelastsubmitted.Enabled = False

Me.closesubmit.SetFocus
Me.submit.Enabled = False
Else
If rstemp!datelastsubmitted < currentdatesubmit Then
rstemp.edit
rstemp!datelastsubmitted = currentdatesubmit
rstemp!datenextsubmit = datenextsubmit
rstemp!TOIL = toilendofmonth
rstemp.Update
DoCmd.SendObject , , acFormatHTML, "(e-mail address removed)", , , strsubject,
strbody, False, False
Me.currentdatesubmit = ""
Me.datenextsubmit = ""
Me.TOIL = ""
Me.availablehours = ""
Me.toilaccduringmonth = ""
Me.toilendofmonth = ""
Me.expenses = ""
Me.totalhours = ""
Me.datelastsubmitted = ""

Me.currentdatesubmit.Enabled = False
Me.datenextsubmit.Enabled = False
Me.TOIL.Enabled = False
Me.availablehours.Enabled = False
Me.toilaccduringmonth.Enabled = False
Me.toilendofmonth.Enabled = False
Me.expenses.Enabled = False
Me.totalhours.Enabled = False
Me.datelastsubmitted.Enabled = False

Me.closesubmit.SetFocus
Me.submit.Enabled = False
'Else
'response = MsgBox(("You cannot submit a month pior to the last
submission date."), vbOKOnly)
'Me.currentdatesubmit.SetFocus
End If
End If

rstemp.close
Set rstemp = Nothing

Exit_submit_Click:
Exit Sub
Err_submit_Click:
'MsgBox Err.Description
Resume Exit_submit_Click
End Sub
 
Rhys said:
I have linked our existing Access database to SQLand am running the
tables purely in SQL server 2008 having deleted the Acess tables and
renamed the linked tables to what the Access tables used to be
called. However when i try to run some code now it doesnt work.
Clicking on the 'submit' button normally inserts some information
from the form screen into 'tblusers' and then sends an email to an
individual letting them know that the submission has happened etc but
now since linking to SQL tables it wont work. If I comment out the
recordset code then the email will be sent but obviously no data will
be inserted into 'tblusers' so it doesnt seem to like the code
concerning rstemp and where I attempt to open a recordset on
'tblusers'. Does SQL require different code for the insertion to
happen?
Click to expand...

Did you define primary keys on your SQL Server tables? If not your links
are most likely read-only. See if you can make edits directly in the link
datasheet to test this.
 
Hi Rick, there is a primary key on the table. If i amend information in a
form manually it updates the tables in SQL (i.e. go into a form and change
the date fields that im trying to update viat the code. however i cant amend
the information directly within SQL, after i execute the changes it sets them
back to what they were, i dont know much about SQL at the moment to know if
this is by design. I dont know if SQL rejects the connection via the code
ive written and subsequently refuses to update the tables, like it does when
im directly in SQL itself?

Thanks for your help, any further ideas would be greatly appreciated!

Rhys.
 
Rhys said:
Hi Rick, there is a primary key on the table. If i amend information
in a form manually it updates the tables in SQL (i.e. go into a form
and change the date fields that im trying to update viat the code.
however i cant amend the information directly within SQL, after i
execute the changes it sets them back to what they were, i dont know
much about SQL at the moment to know if this is by design. I dont
know if SQL rejects the connection via the code ive written and
subsequently refuses to update the tables, like it does when im
directly in SQL itself?

Thanks for your help, any further ideas would be greatly appreciated!
Click to expand...

There are case where Recordsets against ODBC sources need to be opened with
the dbSeeChanges option, but if you need that I would expect you to get an
error message stating that you do.

Do you get any error messages? Have you turned error handling off such that
error messages are not being displayed?

If you can't get it going post your code.
 
Hi Rick, code is posted below:
ive tried converting it to ado too and i get the same problem, clicking on
the submit button does nothing, no errors, just nothing happens. however i
have similar coding written in DAO on other forms that works fine that uses
recordsets to sum up values from tables and display them in fields, although
they are not updating existing fields as this code is trying to do. Ive tried
dbseechanges too and that makes no difference either, and again no errors.

im confused (.com!)

Thanks,

Rhys.

Private Sub Submit_Click()
On Error GoTo Err_submit_Click

Me.Expense.Enabled = False

Dim rstemp As Recordset
Dim dls As Date
Dim straddress As String
Dim strbody As String
Dim strsubject As String
strsubject = "End of Month Submission from" & " " & loginname & "" & " " &
"for" & " " & Me.submitmonth & " " & Me.submityear
strbody = "Expenses:" & " " & "£" & Me.expenses & vbCrLf & "Available
Hours:" & " " & Me.totalhours & vbCrLf & "Hours Worked:" & " " &
Me.availablehours & vbCrLf & _
"TOIL @ Start of Month:" & " " & Me.toil & vbCrLf & "TOIL Accrued During
Month:" & " " & Me.toilaccduringmonth & vbCrLf & _
"New TOIL Figure:" & " " & Me.toilendofmonth
Set rstemp = CurrentDb.OpenRecordset("tblusers")
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname

If rstemp.NoMatch Then
rstemp.AddNew
rstemp!loginname = loginname
rstemp!datelastsubmitted = currentdatesubmit
rstemp!datenextsubmit = datenextsubmit
rstemp!toil = toilendofmonth
rstemp.Update
DoCmd.SendObject , , acFormatHTML, "(e-mail address removed)", ,
, strsubject, strbody, False, False
Me.currentdatesubmit = ""
Me.datenextsubmit = ""
Me.toil = ""
Me.availablehours = ""
Me.toilaccduringmonth = ""
Me.toilendofmonth = ""
Me.expenses = ""
Me.totalhours = ""
Me.datelastsubmitted = ""

Me.currentdatesubmit.Enabled = False
Me.datenextsubmit.Enabled = False
Me.toil.Enabled = False
Me.availablehours.Enabled = False
Me.toilaccduringmonth.Enabled = False
Me.toilendofmonth.Enabled = False
Me.expenses.Enabled = False
Me.totalhours.Enabled = False
Me.datelastsubmitted.Enabled = False

Me.closesubmit.SetFocus
Me.submit.Enabled = False
Else
If rstemp!datelastsubmitted < currentdatesubmit Then
rstemp.edit
rstemp!datelastsubmitted = currentdatesubmit
rstemp!datenextsubmit = datenextsubmit
rstemp!toil = toilendofmonth
rstemp.Update
DoCmd.SendObject , , acFormatHTML, "(e-mail address removed)", ,
, strsubject, strbody, False, False
Me.currentdatesubmit = ""
Me.datenextsubmit = ""
Me.toil = ""
Me.availablehours = ""
Me.toilaccduringmonth = ""
Me.toilendofmonth = ""
Me.expenses = ""
Me.totalhours = ""
Me.datelastsubmitted = ""

Me.currentdatesubmit.Enabled = False
Me.datenextsubmit.Enabled = False
Me.toil.Enabled = False
Me.availablehours.Enabled = False
Me.toilaccduringmonth.Enabled = False
Me.toilendofmonth.Enabled = False
Me.expenses.Enabled = False
Me.totalhours.Enabled = False
Me.datelastsubmitted.Enabled = False

Me.closesubmit.SetFocus
Me.submit.Enabled = False
'Else
'response = MsgBox(("You cannot submit a month pior to the last
submission date."), vbOKOnly)
'Me.currentdatesubmit.SetFocus
End If
End If

rstemp.close
Set rstemp = Nothing

Exit_submit_Click:
Exit Sub
Err_submit_Click:
'MsgBox Err.Description
Resume Exit_submit_Click
End Sub
 
Rhys said:
Hi Rick, code is posted below: [snip]
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname
Click to expand...

Not really a SQL Server problem per-se. You cannot use "Seek" on any linked
table.

Instead use a SQL statement for the Recordset that has a WHERE clause for
the loginname you want. Then you either get the desired record immediately
upon opening the Recordset or you get nothing.
 
Solved it by removing all of the code and just using UPDATE tblusers SET etc.

thanks for pointing me in the right direction Rick


Rick Brandt said:
Rhys said:
Hi Rick, code is posted below: [snip]
rstemp.Index = "PrimaryKey"
rstemp.Seek "=", loginname
Click to expand...

Not really a SQL Server problem per-se. You cannot use "Seek" on any linked
table.

Instead use a SQL statement for the Recordset that has a WHERE clause for
the loginname you want. Then you either get the desired record immediately
upon opening the Recordset or you get nothing.
Click to expand...
 
Back
Top