Avatar of Josh2442
Josh2442
 asked on

Find and replace not working In access after tables are linked to SQL Server

We have a forms in access and Find and Replace works fine before the tables are migrated to the ms SQL.Now after I migrated the tables to SQL,Find and Replace takes too much time and I guess i would say it does not work.Please advise.Thanks
Microsoft DevelopmentMicrosoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Barry62

Were you using the find and replace tools in access, or were you running SQL queries?

If you are now using MSSQL, you'll need some queries.

What, specifically, are you trying to find and replace?
EugeneZ

you need to run post migration DB maints: reindex;update stats; etc  and probably consider to review your code for optimization: add indexes , etc
Josh2442

ASKER
It's using the vba code and I am using find and replace tools in access.I dont know anything about the vba code.By the way we have forms and find and replace is a binocular things in access ,when user tries to put the account number,it displays the form for that account number but after I migrated to sql,its taking too much time though the records are at the forms.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Barry62

Sounds like you need to find a vb programmer to modify the code.  Can you access the vba code and copy and paste it here?  Maybe we can modify it for you.
Josh2442

ASKER
Option Compare Database



Private Sub Command85_Click()
On Error GoTo Err_Command85_Click


    Screen.PreviousControl.SetFocus
    DoCmd.FindNext

Exit_Command85_Click:
    Exit Sub

Err_Command85_Click:
    MsgBox Err.Description
    Resume Exit_Command85_Click
   
End Sub

Private Sub Detail_Click()

End Sub
Private Sub Command86_Click()
On Error GoTo Err_Command86_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command86_Click:
    Exit Sub

Err_Command86_Click:
    MsgBox Err.Description
    Resume Exit_Command86_Click
   
End Sub

Private Sub Return_to_Main_Click()

End Sub
Private Sub Command87_Click()
On Error GoTo Err_Command87_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Collateral Control Switchboard Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command87_Click:
    Exit Sub

Err_Command87_Click:
    MsgBox Err.Description
    Resume Exit_Command87_Click
   
End Sub
Private Sub Command88_Click()
On Error GoTo Err_Command88_Click


    DoCmd.GoToRecord , , acFirst

Exit_Command88_Click:
    Exit Sub

Err_Command88_Click:
    MsgBox Err.Description
    Resume Exit_Command88_Click
   
End Sub
Private Sub Command89_Click()
On Error GoTo Err_Command89_Click


    DoCmd.GoToRecord , , acLast

Exit_Command89_Click:
    Exit Sub

Err_Command89_Click:
    MsgBox Err.Description
    Resume Exit_Command89_Click
   
End Sub
Private Sub Command90_Click()
On Error GoTo Err_Command90_Click


    DoCmd.GoToRecord , , acNext

Exit_Command90_Click:
    Exit Sub

Err_Command90_Click:
    MsgBox Err.Description
    Resume Exit_Command90_Click
   
End Sub
Private Sub Command91_Click()
On Error GoTo Err_Command91_Click


    DoCmd.GoToRecord , , acPrevious

Exit_Command91_Click:
    Exit Sub

Err_Command91_Click:
    MsgBox Err.Description
    Resume Exit_Command91_Click
   
End Sub
Private Sub Command92_Click()
On Error GoTo Err_Command92_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Command92_Click:
    Exit Sub

Err_Command92_Click:
    MsgBox Err.Description
    Resume Exit_Command92_Click
   
End Sub
Private Sub Command127_Click()
On Error GoTo Err_Command127_Click


    DoCmd.GoToRecord , , acNewRec

Exit_Command127_Click:
    Exit Sub

Err_Command127_Click:
    MsgBox Err.Description
    Resume Exit_Command127_Click
   
End Sub
Private Sub Exit_Database_Click()
On Error GoTo Err_Exit_Database_Click

    Dim stDocName As String

    stDocName = "Exit"
    DoCmd.RunMacro stDocName

Exit_Exit_Database_Click:
    Exit Sub

Err_Exit_Database_Click:
    MsgBox Err.Description
    Resume Exit_Exit_Database_Click
   
End Sub
Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click


    DoCmd.GoToRecord , , acNewRec

Exit_AddRecord_Click:
    Exit Sub

Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click
   
End Sub
Private Sub Command138_Click()
On Error GoTo Err_Command138_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Collateral Control Shipping Form"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command138_Click:
    Exit Sub

Err_Command138_Click:
    MsgBox Err.Description
    Resume Exit_Command138_Click
   
End Sub
Private Sub Command139_Click()
On Error GoTo Err_Command139_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "OpenCollTasksForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command139_Click:
    Exit Sub

Err_Command139_Click:
    MsgBox Err.Description
    Resume Exit_Command139_Click
   
End Sub
Private Sub Command142_Click()
On Error GoTo Err_Command142_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Loan Number Cross Reference FORM"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command142_Click:
    Exit Sub

Err_Command142_Click:
    MsgBox Err.Description
    Resume Exit_Command142_Click
   
End Sub

Private Sub Form_Load()

End Sub
Josh2442

ASKER
Actually for the find button its using command86--

Private Sub Command86_Click()
On Error GoTo Err_Command86_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command86_Click:
    Exit Sub

Err_Command86_Click:
    MsgBox Err.Description
    Resume Exit_Command86_Click
   
End Sub
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

So, Josh, can you enlighten us on exactly what worked for you?

Did the RunCommand resolve the problem, or was it that you needed to select the text in that previous control after setting the focus to the control.