Access Database After Table Migration To SQL

[DoubleD]

Hi


I have split my database moving the tables to SQL. I can open all tables from the front end database, all reports run fine, and forms load the correct data.
I have a problem with a sub which throws an error, There is something wrong in ReturnRecordContents Table = ThisWeeksShifts
Any reports that access the table are correct. I have checked the table in SQL and the index is there

The code is below, has anyone any idea what the problem maybe

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
Dim Response As Integer
Dim TargetTable As String, TargetIndex As String, TargetSearchValue As Long
Dim NoUnmatchedMessage As Boolean, RecordContents As Long
Dim NumberOfFields As Long, TargetSearchValue2 As String


If Date >= Me![DateWeekEndRoutineDueSubform].Form![txtCurrentWeekStartDate] Then
RecordContents = 0
TargetTable = "ThisWeeksShifts"
TargetIndex = "Targets"
TargetSearchValue = 0
TargetSearchValue2 = 0
NumberOfFields = 2
NoUnmatchedMessage = True
RecordContents = ReturnRecordContents(TargetTable, TargetIndex, _
TargetSearchValue, TargetSearchValue2, NumberOfFields, _
NoUnmatchedMessage, "DriverTargetNet", "DriverTargetNetLTD")
If RecordContents <> 8 Then
MsgBox ("Please ensure that all Drivers have been allocated targets")
Exit Sub
End If


End If

[pbaldy]

What does that function look like? Seek doesn't work on linked tables, if it uses that.

[ssanfu]

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
Dim Response As Integer
Dim TargetTable As String, TargetIndex As String, TargetSearchValue As Long
Dim NoUnmatchedMessage As Boolean, RecordContents As Long
Dim NumberOfFields As Long, TargetSearchValue2 As String

Why is TargetSearchValue declared as a Long and TargetSearchValue2 declared as a String???
Shouldn't TargetSearchValue2 also be declared as a LONG??

[DoubleD]

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
Dim Response As Integer
Dim TargetTable As String, TargetIndex As String, TargetSearchValue As Long
Dim NoUnmatchedMessage As Boolean, RecordContents As Long
Dim NumberOfFields As Long, TargetSearchValue2 As String

Why is TargetSearchValue declared as a Long and TargetSearchValue2 declared as a String???
Shouldn't TargetSearchValue2 also be declared as a LONG??

I am not sure about the long and string but it doesnt cause a problem in a single database or a split database.
this database was originally Access 97.
I did change it to long in the front end connecting to SQL but it didnt change anything.
I m thinking it is something to do with the index not working when using the linked SQL tables, the indexes are all created on the SQL server but I am not sure if the Front End can connect to them

[pbaldy]

Did you miss post 2?

[DoubleD]

What does that function look like? Seek doesn't work on linked tables, if it uses that.

this particular one doesnt use seek but scanning through the modules I can see it used on other forms, thats another days work so
this particular sub searches 2 fields for zeros and depending on the number of zeros displays a message.
It works in a split database but not with the tables on SQL server.
It isnt my development so trying to work through the forms

[pbaldy]

Well, it's hard to say why without seeing the code.

[DoubleD]

Hi Paul
All the code is really there, it runs on clicking the exit button on the form
On exiting the form, it checks the date and the values in two fields of thisweeksshifts, they are long in access and int in SQL
if the fields are empty it pops a message, if they are not empty it goes to the next step of offering a report, then exits the form
I tried adding an index on the the table in the front end, I didnt get the original error message but it didnt go any further

Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click


Dim Response As Integer
Dim TargetTable As String, TargetIndex As String, TargetSearchValue As Long
Dim NoUnmatchedMessage As Boolean, RecordContents As Long
Dim NumberOfFields As Long, TargetSearchValue2 As String
' dbs.Execute "CREATE INDEX Targets ON ThisWeeksShifts " _
' & "(DriverTargetNET, DriverTargetNETLTD);"
If Date >= Me![DateWeekEndRoutineDueSubform].Form![txtCurrentWeekStartDate] Then
RecordContents = 0
TargetTable = "ThisWeeksShifts"
TargetIndex = "Targets"
TargetSearchValue = 0
TargetSearchValue2 = 0
NumberOfFields = 2
NoUnmatchedMessage = True
RecordContents = ReturnRecordContents(TargetTable, TargetIndex, _
TargetSearchValue, TargetSearchValue2, NumberOfFields, _
NoUnmatchedMessage, "DriverTargetNet", "DriverTargetNetLTD")
If RecordContents <> 8 Then
MsgBox ("Please ensure that all Drivers have been allocated targets")
Exit Sub
End If


End If


Response = MsgBox("Do you wish to check for duplicate shifts?", vbYesNo)
If Response = vbYes Then
Me.Visible = False
DoCmd.OpenReport "PossibleDriverDuplications", acViewPreview
Else
DoCmd.Close
DoCmd.OpenForm "MainSwitchBoard"
End If






Exit_cmdExit_Click:

Exit Sub


Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click

End Sub

[pbaldy]

I was looking for the code for ReturnRecordContents. Youd have to use a passthrough query to create an index, but I've never needed to do it dynamically like this. What line throws the error, and exactly what is it?

[DoubleD]

I was hoping to avoid any extra queries for migrating, will probably have to leave it as split for the moment
I will have to go and check where it breaks, I will get back later

[DoubleD]

Hi Paul

Just running it as a new sub, I get compile error sub or function not defined,, ReturnRecordContents is highlighted

[DoubleD]

Hi Phil
Here is the original function, the db would be changed for the sql database

Public Function ReturnRecordContents(TargetTable, TargetIndex, _
TargetSearchValue, TargetSearchValue2, NumberOfFields, NoUnmatchedMessage, ParamArray FieldName())
Dim I As Long
ReDim ReturnedValue(NumberOfFields)
On Error GoTo ErrorRoutine
Dim dbs As Database, rst As Recordset
Dim ErrorString As String, ErrorExpression As Integer
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(TargetTable, dbOpenTable)
rst.Index = TargetIndex
If TargetSearchValue2 = "NONE" Then
rst.Seek "=", TargetSearchValue
Else
rst.Seek "=", TargetSearchValue, TargetSearchValue2
End If
If rst.NoMatch Then GoTo NoMatchRoutine
For I = 0 To (NumberOfFields - 1)
ReturnedValue(I) = rst.Fields(FieldName(I))

Next I

rst.Close
Set dbs = Nothing

Exit_ReturnRecordContents:
ReturnRecordContents = 1
Exit Function


ErrorRoutine:
ErrorString = "Ooops! There is something wrong in ReturnRecordContents " & vbCrLf
ErrorString = ErrorString & " Table: = " & TargetTable & vbCrLf
ErrorString = ErrorString & "Please write this message down and click OK" & vbCrLf
ErrorString = ErrorString & "to shut the database down."
ErrorExpression = MsgBox(ErrorString, vbCritical)
ReturnRecordContents = 9
Exit Function


NoMatchRoutine:
If NoUnmatchedMessage Then
ReturnRecordContents = 8
Exit Function
End If
ErrorString = "Ooops! There is no record with that match"
ErrorExpression = MsgBox(ErrorString, vbCritical)
Exit Function


End Function

[DoubleD]

Hi Paul
I see that function does use seek, it is called in a lot of modules
Can you suggest anything

[pbaldy]

Yes, it would be far more efficient to simply open the recordset to the desired record(s). You're pulling the entire table across the network, then filtering for a record. Below would only bring the matching records across. In general:

Set rst = dbs.OpenRecordset("SELECT * FROM TargetTable WHERE KeyField = 123", dbOpenDynaset)

You could adapt your function to pass the 3 values, then:

Set rst = dbs.OpenRecordset("SELECT * FROM " & TargetTable & " WHERE " & KeyFieldVariable & " = " & ValueVariable, dbOpenDynaset)

Note I also change dbOpenTable to dbOpenDynaset. You'd also test for the recordset being EOF instead of NoMatch.

[DoubleD]

Thanks Paul
I will give that ago, it will be awhile though
The tables that are involved are only small tables 150 to 200 records so there is no real network issue.
the database was written in 1995 and the developer expected it to be in use for a couple of years, still going
thanks