UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Change Link To Access Db From Excel - Macro/manual, Access 2016    
 
   
bakersburg9
post Aug 16 2019, 12:31 PM
Post#1



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


I have an Excel workbook that has worksheets that are linked to my local hard drive, C:\Users\Steve.rollins\Documents\SteveR

two challenges - I work with lots of databases with similar names, and I forget which one it's linked to - it's not as simple as just right-clicking on the worksheet tab, and select an item from the drop-down menu, and I researched it, and it's not as easy as you would think - like if you have the old path and the "new" path (I want to put it on the network), and ... easy-peazy, you're done - I found the following on line - would this even work ? It's saying you just set up a form with 2 command buttons - would the command buttons have to be in a form in the database being linked to ?

CODE
Private Sub Select_Click()
'Requires reference to Microsoft Office 12.0 Object Library.
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
    'Allow user to make multiple selections in dialog box.
    .AllowMultiSelect = True
    'Set the title of the dialog box.
    .Title = "Choose Database Network Location"
    'Clear out the current filters, and add our own.
    .Filters.Clear
    .Filters.Add "Database", "*.accdb"
    .Filters.Add "All Files", "*.*"
    'Show the dialog box. If the .Show method returns True, the
    'user picked at least one file. If the .Show method returns
    'False, the user clicked Cancel.
    If .Show = True Then
    'Loop through each file selected and add it to the list box.
    For Each varFile In .SelectedItems
    Me.FileList.AddItem varFile
    Next
    Else
    MsgBox "You clicked Cancel in the file dialog box."
    End If
    End With
End Sub

Private Sub Update_Click()
Me.FileList.SetFocus
Me.FileList.Selected(0) = True
'If file name has been selected (Default value is 1 so must be longer to show selection)
If Len(Me.FileList.Value) > 1 Then
'Change datapath to selected network drive
Dim tdef As TableDef
Dim Strnew As String
Strnew = ";database=" & Me.FileList
For Each tdef In CurrentDb.TableDefs
If Left(tdef.Connect, Len(";Database=")) = ";database=" Then
If Not InStr(tdef.Connect, "mydb.accdb") Then
Debug.Print tdef.Name; tdef.Connect
tdef.Connect = Strnew
tdef.RefreshLink
End If
End If
Next
End If
'Close form and refresh main form
DoCmd.Close
DoCmd.RunMacro "Refresh"
End Sub
Go to the top of the page
 
dale.fye
post Aug 16 2019, 01:01 PM
Post#2



Posts: 161
Joined: 28-March 18
From: Virginia


That code changes the links in the tables in an Access database to a new back-end file. It has nothing to do with Excel.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
June7
post Aug 16 2019, 01:12 PM
Post#3



Posts: 891
Joined: 25-January 16



That code would be in Access and it changes linked table path to an accdb file. It does not change Excel link to Access.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
bakersburg9
post Aug 16 2019, 01:29 PM
Post#4



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


June7, Dale - thanks - so, offhand, do you know more or less how to do it ? I don't need you to give me step-by-step detailed instructions, but just a few words off the top of your head - I don't have a clue, and couldn't find an answer on the 'net, although I'm sure it's out there somewhere... but to me, it shouldn't be that hard.

Thanks again!

Steve
Go to the top of the page
 
bakersburg9
post Aug 16 2019, 01:29 PM
Post#5



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


June7, Dale - thanks - so, offhand, do you know more or less how to do it ? I don't need you to give me step-by-step detailed instructions, but just a few words off the top of your head - I don't have a clue, and couldn't find an answer on the 'net, although I'm sure it's out there somewhere... but to me, it shouldn't be that hard.

Thanks again!

Steve
Go to the top of the page
 
June7
post Aug 16 2019, 01:45 PM
Post#6



Posts: 891
Joined: 25-January 16



I used Excel Macro recorder to generate VBA code while setting external link to Access db and to remove link. Then I ran the generated code for creating link and it worked. Here is what the recorder produced:
CODE
Sub Macro2()
'
' Macro2 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\Owner\June\LL\Umpires.accdb;Mode=Share Deny Wri" _
        , _
        "te;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engin" _
        , _
        "e Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Ne" _
        , _
        "w Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Co" _
        , _
        "mpact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:By" _
        , "pass UserInfo Validation=False"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Rates")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = "C:\Users\Owner\June\LL\Umpires.accdb"
        .ListObject.DisplayName = "Table_Umpires.accdb"
        .Refresh BackgroundQuery:=False
    End With
    With ActiveWorkbook.Connections("Connection")
        .Name = "Umpires"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Umpires").Refresh
End Sub

Sub Macro3()
'
' Macro3 Macro
'

'
    Range("Table_Umpires.accdb[#All]").Select
    Selection.ListObject.QueryTable.Delete
    Selection.ClearContents
End Sub


This post has been edited by June7: Aug 16 2019, 02:06 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
bakersburg9
post Aug 16 2019, 02:02 PM
Post#7



Posts: 5,573
Joined: 2-November 04
From: Downey, CA


June7,
I saw what you posted - I forgot to mention - I'm ok with just removing the link and leaving the data there - that may be a lot easier option - it's just as you know, if you have data linked to an Access db, there's some limitations / restrictions

Steve
Go to the top of the page
 
June7
post Aug 16 2019, 02:03 PM
Post#8



Posts: 891
Joined: 25-January 16



Okay, remove link and leave data:

ActiveWorkbook.Connections("Umpires").Delete

But if I manually select the range including the header row and try to delete I get warning message about deleting query.

This post has been edited by June7: Aug 16 2019, 02:07 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 10:33 AM