Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 7 votes, 5.00 average. Display Modes
Old 08-28-2009, 06:23 AM   #1
earnhardt08
Registered User
 
Join Date: Dec 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
earnhardt08 is on a distinguished road
update linked table via VBA

I have a couple Access tables linked to tables in SQL Server 2005. I'm using the "Linked Table Manger" to update these tables; is it possible to do this programmatically using VBA? I'd like to create a form with a button, or something similar.

Thanks!
jason

earnhardt08 is offline   Reply With Quote
Old 08-28-2009, 07:10 AM   #2
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 796 Times in 783 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: update linked table via VBA

I use this in a form
Code:
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = False
    tblDB = myFolder & "Folder\Database.MDB"
    If Dir(tblDB) = "" Then
        MsgBox "No table database has been found, " & vbCr & vbCr & _
                "This application will not work, so its beeing closed", vbCritical
        Application.Quit
    End If
                       
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "Folder\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done relinking ... "
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    

    DoCmd.OpenForm "frmMain"
    DoCmd.Close acForm, Me.Name
    
End Sub
Function myFolder()
    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
End Function
Which does that very thing for MDB linked tables, pretty sure you can addapt it to work with SQL Server.

Good luck ! & Welcome to AWF
__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 08-28-2009, 09:12 AM   #3
NigelShaw
Newly Registered User
 
NigelShaw's Avatar
 
Join Date: Jan 2008
Location: Leicester UK
Posts: 1,560
Thanks: 8
Thanked 67 Times in 45 Posts
NigelShaw is on a distinguished road
Re: update linked table via VBA

Hi,

i use this

Code:
Dim CurDB As DAO.Database, tdfLinked As DAO.TableDef
    Dim TBDef As DAO.TableDef, InstallPath As String
    Dim DBPath As String
    Dim strBEPath As String

    strBEPath = DLookup("qryBELocation", "BEPath")
    DBPath = Application.CurrentProject.Path
    
    Set CurDB = CurrentDb
    For Each TBDef In CurDB.TableDefs
            If Len(TBDef.Connect) > 0 Then
            TBDef.Connect = ";DATABASE=" & DBPath & "strBEPath

            TBDef.RefreshLink
        End If
    Next TBDef

exit_RunFirstTime:
    Set CurDB = Nothing
    Set tdfLinked = Nothing
    Set TBDef = Nothing
    Exit Sub

err_RunFirstTime:
    MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbcrf, "An Error has occured in refresh tables"
    Resume exit_RunFirstTime
basically, it gets the path from the table holding the full name of the back. eg. "MyBackEnd.accdb"
Its also self sufficient so the as long as the BE is in the same location as the FE, it doesnt matter where the folder they are in is moved to.

thats it


regs


Nigel

__________________
Never, ever, argue with an idiot. They'll drag you down to their level and beat you to death with experience!

If i have helped you in any way, please tip my scales or say a little thanks
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Chars

Checkout my splash screen tool
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
for a free download.
NigelShaw is offline   Reply With Quote
Old 12-04-2013, 06:09 AM   #4
baldmosher
Newly Registered User
 
Join Date: Oct 2013
Location: Salford, GB
Posts: 9
Thanks: 2
Thanked 0 Times in 0 Posts
baldmosher is on a distinguished road
Re: update linked table via VBA

Sorry to bring up an old thread but it seemed more relevant than starting a new one.... mainly add my 2p, as I worked with a few different ideas and came up with this little function:

Code:
Public Sub acU_RelinkTables(ByVal OldBasePath As String, ByVal NewBasePath As String)
'v1.00 2013-12-04 14:13
'Tweaked by Tom Parish 2013-12-04 baldmosher.com
'pass old & new path to update all linked tables
'and it will go through all the tables in your database and link them to the new location
'Original source: Written by John Hawkins 20/9/99 fabalou.com
'via database.ittoolbox.com/groups/technical-functional/access-l/how-to-programme-the-linked-table-manager-using-vba-in-ms-access-5185870
'Syntax:
' acU_RelinkTables("c:\Users\Yourname\Databases\", "\\NewShare\Databases\)

Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Dim TdfCurrentPath As String
Dim u As Integer

Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs

Screen.MousePointer = 11  'shows as "working"
'Loop through the tables collection
For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
        TdfCurrentPath = Tdf.Connect
        If InStr(TdfCurrentPath, OldBasePath) > 0 Then  'If the current path needs to be changed
On Error Resume Next
            Tdf.RefreshLink 'Refresh the link
            If Err = 3011 Then GoTo OriginalTdfError    'bypasses change if current linked table isn't found
            Tdf.Connect = Replace(TdfCurrentPath, OldBasePath, NewBasePath)   'Set the new source
            Tdf.RefreshLink 'Refresh the link
            If Err = 3011 Then GoTo EscapeOnError    'likely means error in new path - could be critical
            u = u + 1
On Error GoTo 0
        End If
    End If
OriginalTdfError:
Next 'Goto next table

Screen.MousePointer = 0
MsgBox u & " tables have been relinked from " & OldBasePath & " to " & NewBasePath, vbInformation, "Tables Relinked"

Exit Sub
EscapeOnError:
MsgBox "Possible major error: please ensure OldBasePath and NewBasePath are correct - you will now be returned to Debug", vbExclamation, "WARNING"
On Error GoTo 0
Tdf.Connect = TdfCurrentPath 'return to original
Tdf.RefreshLink 'Refresh the link - errors here means the table was missing before... this needs to be resolved
'NB: to continue from where you left off, drag arrow up to Next
End Sub
EDIT: seems to be working OK now

Last edited by baldmosher; 12-04-2013 at 06:25 AM.
baldmosher is offline   Reply With Quote
Old 12-04-2013, 06:44 AM   #5
Allan.Day
Newly Registered User
 
Join Date: May 2013
Posts: 25
Thanks: 8
Thanked 0 Times in 0 Posts
Allan.Day is on a distinguished road
Re: update linked table via VBA

Hi Nigel

can you upload the sample database?

thank you

allan
Quote:
Originally Posted by NigelShaw View Post
Hi,

i use this

Code:
Dim CurDB As DAO.Database, tdfLinked As DAO.TableDef
    Dim TBDef As DAO.TableDef, InstallPath As String
    Dim DBPath As String
    Dim strBEPath As String

    strBEPath = DLookup("qryBELocation", "BEPath")
    DBPath = Application.CurrentProject.Path
    
    Set CurDB = CurrentDb
    For Each TBDef In CurDB.TableDefs
            If Len(TBDef.Connect) > 0 Then
            TBDef.Connect = ";DATABASE=" & DBPath & "strBEPath

            TBDef.RefreshLink
        End If
    Next TBDef

exit_RunFirstTime:
    Set CurDB = Nothing
    Set tdfLinked = Nothing
    Set TBDef = Nothing
    Exit Sub

err_RunFirstTime:
    MsgBox Err.Number & ": " & Err.Description, vbOKOnly + vbcrf, "An Error has occured in refresh tables"
    Resume exit_RunFirstTime
basically, it gets the path from the table holding the full name of the back. eg. "MyBackEnd.accdb"
Its also self sufficient so the as long as the BE is in the same location as the FE, it doesnt matter where the folder they are in is moved to.

thats it


regs


Nigel
Allan.Day is offline   Reply With Quote
Old 12-04-2013, 09:16 AM   #6
NigelShaw
Newly Registered User
 
NigelShaw's Avatar
 
Join Date: Jan 2008
Location: Leicester UK
Posts: 1,560
Thanks: 8
Thanked 67 Times in 45 Posts
NigelShaw is on a distinguished road
Re: update linked table via VBA

Quote:
Originally Posted by Allan.Day View Post
Hi Nigel

can you upload the sample database?

thank you

allan
Hi

Yes no problem. I'll do it tonight / tomorrow depending on how much my jetlag takes over lol

__________________
Never, ever, argue with an idiot. They'll drag you down to their level and beat you to death with experience!

If i have helped you in any way, please tip my scales or say a little thanks
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Chars

Checkout my splash screen tool
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
for a free download.
NigelShaw is offline   Reply With Quote
Reply

Tags
link , linked table , manager , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not able to update a table in VBA Access code Arasi Forms 2 04-15-2009 04:28 AM
VBA Required to append different rows anilvaghela Modules & VBA 4 09-24-2008 06:00 AM
Can't update records in Linked table TClark14 Tables 1 04-03-2006 07:15 PM
Update bit Field in Access using SQL linked Table manojab Queries 11 03-07-2005 08:49 AM
Updating Table Via VBA Help TimH Modules & VBA 2 01-09-2003 12:26 PM




All times are GMT -8. The time now is 10:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World