• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

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
0
Josh2442
Asked:
Josh2442
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Barry62Commented:
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?
0
 
Eugene ZCommented:
you need to run post migration DB maints: reindex;update stats; etc  and probably consider to review your code for optimization: add indexes , etc
0
 
Josh2442Author Commented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Barry62Commented:
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.
0
 
Josh2442Author Commented:
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
0
 
Josh2442Author Commented:
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
0
 
Dale FyeCommented:
Josh,

First thing you need to do is start using a naming convention for your controls.   When you add a control of any kind to a form, Access gives it a default name; in most cases, you should change that name so that when you look at your code, you can tell what the control is (Command86 is meaningless).  There are a number of good web sites with suggestions for your naming convention; I use a combination of the Reddick and the Leszynski methods.  I use this naming convention on all of my objects, all my form/report controls, and all of my variables, constants, and objects created via code.

Secondly, I would strongly recommend abandoning the DoMenuItem method in favor of the RunCommand method.  DoMenuItem is confusing, has many versions, and is hard to debug.  RunCommand has several hundred arguments, but they are all easily recognizable and the argument names are a good indication of what you are trying to accomplish.  There used to be a wonderful website with descriptions and code examples, but it appears to be down.  I'm researching a replacement and will post back.

Having said all that, when the line that sets the focus to your previous control is executed, does it highlight all of the text in that control?
0
 
Dale FyeCommented:
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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now