Changing backend from a button

[jasons]

I have a client that adds several hundred records to the backend everyday. This over time caused the system to slow down as the size of the backend became enormously large. I ended up archive a year's worth of records and after compacting the backend it ran much faster. The issue I now have at hand is the owner would like to be able to access the archived backend to query records. Now I could show him how to change it through the linked table manager but I know a simple button that does the same would go over better. I searched google and came across a person that has code to do the exact task but when I compile it I get a "Argument not optional." error. Here is the code that I added to a module. Can anyone tell me what I am missing from making this work?

Public Function ChangeLinks(strNewPath As String) As Boolean
'************************************************************
' Name: ChangeLinks
' Purpose:
'
' Inputs: strNewPath As String
'
' Returns: Boolean
'
' Author: Arvin Meyer
' Date: September 5, 1998
' Comment: Change the Link to a different back-end database
'
'*************************************************************
On Error GoTo Err_ChangeLinks

Dim db As DAO.Database
Dim i As Integer
Dim strNewPath
Set strNewPath = "C:\test.mdb"

Set db = CurrentDb

For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).Connect) > 0 Then
db.TableDefs(i).Connect = ";DATABASE=" & strNewPath
db.TableDefs(i).RefreshLink
End If
Next i

db.TableDefs.Refresh
ChangeLinks = True

Exit_ChangeLinks:
Set db = Nothing
Exit Function


Err_ChangeLinks:
ChangeLinks = False

Select Case Err

Case 0

Case 3044
Resume Next

Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ChangeLinks
End Select

End Function

Thanks for your help!

 

[spikepl]

"Argument not optional." error in which line? When asking for help always say which error, and WHERE.

 

[GrahamMandeno]

Hi Jason

I can see a few problems in your code, but not one that would give you "Argument not optional".

The first is that you have a duplicate declaration for strNewPath. In the procedure declation you have an argument strNewPath As String. Then, further down you have:
Dim strNewPath
which re-declares the variable as a Variant. You should delete the "Dim..." line.

Also, presumably to test your code, you have this line:
Set strNewPath = "C:\test.mdb"

strNewPath is a String variable, so you should omit the "Set" (which should be used only for Object variables).

When you call the function to test it, you must provide an argument (even if it's only an empty string) because the argument strNewPath is not optional. This is possibly the reason for the error you cite.

Good luck!
Graham

 

[NigelShaw]

i dont think the line

[B]Set strNewPath = "C:\test.mdb[/B]"

should be in the code at all. what you should be doing (based on the function call) would be-

call the function

ChangeLinks("C:\test.mdb")

in the function, it should be

Public Function ChangeLinks(strNewPath As String) As Boolean
'************************************************* ***********
' Name: ChangeLinks
' Purpose:
'
' Inputs: strNewPath As String
'
' Returns: Boolean
'
' Author: Arvin Meyer
' Date: September 5, 1998
' Comment: Change the Link to a different back-end database
'
'************************************************* ************
On Error GoTo Err_ChangeLinks
 
Dim db As DAO.Database
Dim i As Integer
 
Set db = CurrentDb
 
For i = 0 To db.TableDefs.Count - 1
If Len(db.TableDefs(i).Connect) > 0 Then
db.TableDefs(i).Connect = ";DATABASE=" & strNewPath
db.TableDefs(i).RefreshLink
End If
Next i
 
db.TableDefs.Refresh
ChangeLinks = True
 
Exit_ChangeLinks:
Set db = Nothing
Exit Function
 
 
Err_ChangeLinks:
ChangeLinks = False
 
Select Case Err
 
Case 0
 
Case 3044
Resume Next
 
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume Exit_ChangeLinks
End Select
 
End Function

strNewPath is already set up in the function call. you jest need to get it from the function call to the routine


hth


N