msAccess file/connection in vba
-
ok... I think it is just too obvious, so I can't find the answer. I need to change in VBA the data link property (found in File/property) : server name and Db name.
anybody knows?
Thanks,
Erik
-
File/connexion?
-
oups... yep file/connection... sorry
-
Well you told that it was gonna be simple
. -
I need to change that dynamically in VBA.. that's what I can't do...
-
I already tried, unsuccessfully. The closest I came to that was to copy all the objects into a new database, then set the connexion there on another server. I never could make it change once it was set here's the code :
Public Sub test(ByVal sFolder As String)
10 On Error GoTo Gestion
Dim MyApp As Access.Application
20 Set MyApp = New Access.Application
Dim MyTargetApp As Access.Application
30 Set MyTargetApp = New Access.Application
Dim MyObj As Access.AccessObject
Dim TargetPath As String
Dim CopyPath As String
Dim MyRef As Access.Reference
Dim FS As Scripting.FileSystemObject
40 Set FS = New Scripting.FileSystemObject
Dim MyFiles As Scripting.Files
Dim MyFile As Scripting.File
Dim MyFolder As Scripting.Folder
Dim sTargetFolder As String
50 sTargetFolder = "Rebuilt\"
60 If FS.FolderExists(sFolder) Then
70 Set MyFolder = FS.GetFolder(sFolder)
80 Set MyFiles = MyFolder.Files
90 If Not FS.FolderExists(sFolder & sTargetFolder) Then
100 FS.CreateFolder sFolder & sTargetFolder
110 End If
120 For Each MyFile In MyFiles
130 If Len(MyFile.name) > 4 Then
140 If LCase(Right(MyFile.name, 4)) = ".adp" Then
150 CopyPath = MyFile.Path
160 MyApp.OpenAccessProject CopyPath
170 TargetPath = "C:\" & sTargetFolder & MyFile.name
180 MyTargetApp.CreateAccessProject TargetPath
190 MyTargetApp.OpenAccessProject TargetPath
200 MyTargetApp.CurrentProject.OpenConnection MyApp.CurrentProject.BaseConnectionString & ";Application Name=" & MyFile.name
'previous operation changes the string to this__ trying to reset it WITH Application name :
210 MyTargetApp.CurrentProject.OpenConnection "PROVIDER=SQLOLEDB.1;Application Name=" & MyFile.name & ";INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=Documentation;DATA SOURCE=SERVEUR4"
220 On Error Resume Next 'cannot delete the default references and cannot add an existing referrence
230 For Each MyRef In MyTargetApp.References
240 MyTargetApp.References.Remove MyRef
250 Next
260 For Each MyRef In MyApp.References
270 MyTargetApp.References.AddFromFile MyRef.FullPath
280 Next
290 MyTargetApp.CloseCurrentDatabase
300 For Each MyObj In MyApp.CurrentProject.AllForms
310 MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acForm, MyObj.name
320 Next
330 For Each MyObj In MyApp.CurrentProject.AllMacros
340 MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acMacro, MyObj.name
350 Next
360 For Each MyObj In MyApp.CurrentProject.AllModules
370 MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acModule, MyObj.name
380 Next
390 For Each MyObj In MyApp.CurrentProject.AllReports
400 MyApp.DoCmd.CopyObject TargetPath, MyObj.name, acReport, MyObj.name
410 Next
420 On Error GoTo 0
430 MyApp.CloseCurrentDatabase
440 End If
450 End If
460 Next
470 Set MyTargetApp = Nothing
480 Set MyApp = Nothing
490 End If
500 Exit Sub
Gestion:
510 Select Case Err.Number
Case Else
520 Select Case ErrHandler(ModuleName, "ModGlobals", "Test", Err, Erl())
Case ErrResume
530 Resume
540 Case ErrResumeNext
550 Resume Next
560 Case ErrExit
570 MsgBox Err.Description & " : " & Err.Number
580 Exit Sub
590 End Select
600 End Select
End Sub
-
ouch... thought it would be easier
but thanks, I'll give it a try
-
After reading many many pages... I finally found the answer!!
For future reference, here's the article:
http://support.microsoft.com/?kbid=306881
Erik
-
I feel compeled to warn you if you are using SQL Logins that method does not work in Access 2000!
Cheers!
* Noel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply