Solved

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

Posted on 2012-12-21
8
393 Views
Last Modified: 2012-12-26
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
Comment
Question by:Josh2442
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:Barry62
ID: 38713213
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38713218
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
 

Author Comment

by:Josh2442
ID: 38713280
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 8

Expert Comment

by:Barry62
ID: 38713296
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
 

Author Comment

by:Josh2442
ID: 38713319
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
 

Author Comment

by:Josh2442
ID: 38713465
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38713855
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38721976
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question