Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 991
  • Last Modified:

refresh links within Access - Problem with table named passed incorrectly

I am using the code fRefreshLinks from ee.

It works great except when the linked object is an Excel spreadsheet.  The table name is passed incorrectly.

Market ListExcel 5.0
strIn = Market ListExcel 5.0;HDR=YES;IMEX=2;DATABASE=\\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls

The table name concatenates the actual table name and the file type together.  I need it to look like:
Market List Excel5.0 or is Market List;Excel 5.0?

How to I modify the code to rectify this problem.  I beleive the problem is with the following portion of the code:

Function fParsePath(strIn As String) As String
    If Left$(strIn, 4) <> "ODBC" Then
        fParsePath = Right(strIn, Len(strIn) _
                        - (InStr(1, strIn, "DATABASE=") + 8))
                        Debug.Print fParsePath
    Else
        fParsePath = strIn
    End If
End Function

Function fParseTable(strIn As String) As String
    fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
Debug.Print strIn

    Debug.Print fParseTable
End Function


where in the code is the "strIn" being set:

Option Compare Database
Option Explicit

'***************** Code Start **************
'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
'   Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
        lStructSize As Long
        hwndOwner As Long
        hInstance As Long
        strFilter As String
        strCustomFilter As String
        nMaxCustFilter As Long
        nFilterIndex As Long
        strFile As String
        nMaxFile As Long
        strFileTitle As String
        nMaxFileTitle As Long
        strInitialDir As String
        strTitle As String
        flags As Long
        nFileOffset As Integer
        nFileExtension As Integer
        strDefExt As String
        lCustData As Long
        lpfnHook As Long
        lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000


Function TestIt()
    Dim strFilter As String
    Dim lngFlags As Long
    strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _
                    "*.MDA;*.MDB")
    strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
        filter:=strFilter, FilterIndex:=3, flags:=lngFlags, _
        DialogTitle:="Hello! Open Me!")
    ' Since you passed in a variable for lngFlags,
    ' the function places the output flags value in the variable.
    Debug.Print Hex(lngFlags)
End Function


Function GetOpenFile(Optional varDirectory As Variant, _
    Optional varTitleForDialog As Variant) As Variant
' Here's an example that gets an Access database name.
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Specify that the chosen file must already exist,
' don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
    lngFlags = ahtOFN_FILEMUSTEXIST Or _
                ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
    If IsMissing(varDirectory) Then
        varDirectory = ""
    End If
    If IsMissing(varTitleForDialog) Then
        varTitleForDialog = ""
    End If

    ' Define the filter string and allocate space in the "c"
    ' string Duplicate this line with changes as necessary for
    ' more file templates.
    strFilter = ahtAddFilterItem(strFilter, _
                "Access (*.mdb)", "*.MDB;*.MDA")
    ' Now actually call to get the file name.
    varFileName = ahtCommonFileOpenSave( _
                    OpenFile:=True, _
                    InitialDir:=varDirectory, _
                    filter:=strFilter, _
                    flags:=lngFlags, _
                    DialogTitle:=varTitleForDialog)

    If Not IsNull(varFileName) Then
        varFileName = TrimNull(varFileName)
    End If
    GetOpenFile = varFileName
End Function


Function ahtCommonFileOpenSave( _
            Optional ByRef flags As Variant, _
            Optional ByVal InitialDir As Variant, _
            Optional ByVal filter As Variant, _
            Optional ByVal FilterIndex As Variant, _
            Optional ByVal DefaultExt As Variant, _
            Optional ByVal FileName As Variant, _
            Optional ByVal DialogTitle As Variant, _
            Optional ByVal hwnd As Variant, _
            Optional ByVal OpenFile As Variant) As Variant

' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(filter) Then filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(flags) Then flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = hwnd
        .strFilter = filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .flags = flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        ' Didn't think most people would want to deal with
        ' these options.
        .hInstance = 0
        .strCustomFilter = ""
        .nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With


    ' This will pass the desired data structure to the
    ' Windows API, which will in turn it uses to display
    ' the Open/Save As Dialog.
    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If

    ' The function call filled in the strFileTitle member
    ' of the structure. You'll have to write special code
    ' to retrieve that if you're interested.
    If fResult Then
        ' You might care to check the Flags member of the
        ' structure to get information about the chosen file.
        ' In this example, if you bothered to pass in a
        ' value for Flags, we'll fill it in with the outgoing
        ' Flags value.
        If Not IsMissing(flags) Then flags = OFN.flags
        ahtCommonFileOpenSave = TrimNull(OFN.strFile)
    Else
        ahtCommonFileOpenSave = "NoFile"
    End If
End Function


Function ahtAddFilterItem(strFilter As String, _
    strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

    If IsMissing(varItem) Then varItem = "*.*"
    ahtAddFilterItem = strFilter & _
                strDescription & vbNullChar & _
                varItem & vbNullChar
End Function


Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
    Else
        TrimNull = strItem
    End If
End Function
'************** Code End *****************

Function fRefreshLinks() As Boolean
Dim strMsg As String, collTbls As Collection
Dim i As Integer, strDBPath As String, strTbl As String
Dim dbCurr As Database, dbLink As Database
Dim tdfLocal As TableDef
Dim varRet As Variant
Dim strNewPath As String

Const cERR_USERCANCEL = vbObjectError + 1000
Const cERR_NOREMOTETABLE = vbObjectError + 2000

    On Local Error GoTo fRefreshLinks_Err

       'First get all linked tables in a collection
    Set collTbls = fGetLinkedTables

    'now link all of them
    Set dbCurr = currentdb

    For i = collTbls.Count To 1 Step -1
        strDBPath = fParsePath(collTbls(i))
                 Debug.Print strDBPath
       
        strTbl = fParseTable(collTbls(i))
 Debug.Print strTbl
        varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
       
 Debug.Print varRet
        If Left$(strDBPath, 4) = "ODBC" Then
        Else
            If strNewPath <> vbNullString Then
                'Try this first
                strDBPath = strNewPath
            Else
                If Len(Dir(strDBPath)) = 0 Then
                    'File Doesn't Exist, call GetOpenFileName
                    strDBPath = fGetMDBName("'" & strDBPath & "' not found.")
                    If strDBPath = vbNullString Then
                        'user pressed cancel
                        Err.Raise cERR_USERCANCEL
                    End If
                End If
            End If

            'backend database exists
            'putting it here since we could have
            'tables from multiple sources
            Set dbLink = DBEngine(0).OpenDatabase(strDBPath)

            'check to see if the table is present in dbLink
            strTbl = fParseTable(collTbls(i))
            If fIsRemoteTable(dbLink, strTbl) Then
                'everything's ok, reconnect
                Set tdfLocal = dbCurr.TableDefs(strTbl)
                With tdfLocal
                    .Connect = ";Database=" & strDBPath
                    .RefreshLink
                    collTbls.Remove (.Name)
                End With
            Else
                Err.Raise cERR_NOREMOTETABLE
            End If
        End If
    Next
    fRefreshLinks = True
    varRet = SysCmd(acSysCmdClearStatus)
 
fRefreshLinks_End:
    Set collTbls = Nothing
    Set tdfLocal = Nothing
    Set dbLink = Nothing
    Set dbCurr = Nothing
    Exit Function
fRefreshLinks_Err:
    fRefreshLinks = False
    Select Case Err
        Case 3059:

        Case cERR_USERCANCEL:
            MsgBox "No Database was specified, couldn't link tables.", _
                    vbCritical + vbOKOnly, _
                    "Error in refreshing links."
            Resume fRefreshLinks_End
        Case cERR_NOREMOTETABLE:
            MsgBox "Table '" & strTbl & "' was not found in the database" & _
                    vbCrLf & dbLink.Name & ". Couldn't refresh links", _
                    vbCritical + vbOKOnly, _
                    "Error in refreshing links."
            Resume fRefreshLinks_End
        Case Else:
            strMsg = "Error Information..." & vbCrLf & vbCrLf
            strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
            strMsg = strMsg & "Description: " & Err.Description & vbCrLf
            strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            MsgBox strMsg, vbOKOnly + vbCritical, "Error"
            Resume fRefreshLinks_End
    End Select
End Function
Function fGetMDBName(strIn As String) As String
'Calls GetOpenFileName dialog
Dim strFilter As String

   
    strFilter = ahtAddFilterItem(strFilter, "Access Database(*.mdb;*.mda;*.mde;*.mdw) ", "*.mdb; *.mda; *.mde; *.mdw")
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    fGetMDBName = ahtCommonFileOpenSave(filter:=strFilter, OpenFile:=True, DialogTitle:=strIn, flags:=ahtOFN_HIDEREADONLY)
Debug.Print fGetMDBName

End Function
Function fIsRemoteTable(dbRemote As Database, strTbl As String) As Boolean
Dim tdf As TableDef
    On Error Resume Next
    Set tdf = dbRemote.TableDefs(strTbl)
    fIsRemoteTable = (Err = 0)
    Set tdf = Nothing
End Function

Function fGetLinkedTables() As Collection
'Returns all linked tables
    Dim collTables As New Collection
    Dim tdf As TableDef, db As Database
    Set db = currentdb
    db.TableDefs.Refresh
    For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                If Left$(.Connect, 4) = "ODBC" Then
                '    collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
                'ODBC Reconnect handled separately
                Else
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
                End If
        Debug.Print .Name
            End If
        End With
    Next
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Function fParsePath(strIn As String) As String
    If Left$(strIn, 4) <> "ODBC" Then
      Debug.Print strIn
        fParsePath = Right(strIn, Len(strIn) _
                        - (InStr(1, strIn, "DATABASE=") + 8))
                        Debug.Print fParsePath
    Else
        fParsePath = strIn
    End If
End Function

Function fParseTable(strIn As String) As String
    fParseTable = Left$(strIn, InStr(2, strIn, ";") - 1)
    Debug.Print fParseTable
End Function



Thanks,

Karen
0
Karen Schaefer
Asked:
Karen Schaefer
  • 11
  • 8
  • 5
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try

Function fParseTable(strIn As String) As String
 '   fParseTable = Left$(strIn, InStr(2, strIn, ";") - 1)

    fParseTable = Mid(strIn, InStrRev(strIn, "\") + 1)    

    Debug.Print fParseTable
End Function

you should get the name of the excel file from

strIn = Market ListExcel 5.0;HDR=YES;IMEX=2;DATABASE=\\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls


which is  
 NCP DB Market Name Reconcile.xls



0
 
Karen SchaeferAuthor Commented:
It is not the excel path I am having difficulty with.

It is the Market ListExcel 5.0 portion of the string - should it read

Market List Excel 5.0 or Market List;Excel 5.0 ...

Karen
0
 
Karen SchaeferAuthor Commented:
Again what portion of the code sets the value for strIN?

K
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
rockiroadsCommented:
if u just want a semi colon in front of excel, try this

strIn = replace(strIn, "Excel 5.0",";Excel 5.0")

0
 
Rey Obrero (Capricorn1)Commented:
the strIN  is not set in the codes that you posted.


from the VBA window  hit CTRL+F  or Edit > Find

type strIN in the find what box and click  FInd Next

0
 
Karen SchaeferAuthor Commented:
Which is the correct syntax - my code seems to stop on this issue - so I thought that this could have been a typo issue.

Does anyone know what the correct syntax should be?

K
0
 
Karen SchaeferAuthor Commented:
I get the following error

Functin: fRefreshLinks
Descp: Unrecognized database format "\\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls"
error#: 3343

I thought that I had identified the issue - may not - Can anyone explain why I am getting this error?
K
0
 
rockiroadsCommented:
Karen, bit confused what u want to do, do u want *.xls to appear in the filter with a default name?

if so, u can do this

add this filter

    strFilter = ahtAddFilterItem(strFilter, "Excel 5.0 (*.xls)")


then in the call, specify filename e.g. specify abc.xls

    MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:\", _
        filter:=strFilter, FilterIndex:=3, flags:=lngFlags, FileName:="abc.xls", _
        DialogTitle:="Hello! Open Me!")
0
 
Karen SchaeferAuthor Commented:
I want to refresh the link to an Excel table called Market List and it is located in the \\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls" file.

So what do I need to do to refresh the link of this excel table - what portion of the code to I need to modify?

K
0
 
Rey Obrero (Capricorn1)Commented:
run this query to get the right information


SELECT MSysObjects.Connect, MSysObjects.Database, MSysObjects.ForeignName, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));
0
 
Rey Obrero (Capricorn1)Commented:
and i think it should just be

strIn = Excel 5.0;HDR=YES;IMEX=2;DATABASE=\\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls
0
 
Karen SchaeferAuthor Commented:
I ran your query suggestions and if I use your latest strin string how does it now what table to refresh the link?

K
0
 
Rey Obrero (Capricorn1)Commented:
they are placed in a collection by this function

Function fGetLinkedTables() As Collection

0
 
rockiroadsCommented:
Why do u have a file dialog in

do u prompt the users for a xls, then refresh current link?

why dont u do this


delete current link., then relink

e.g.

    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "accestablename", "\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls",true

last parameter true is used if excel sheet contains headings

0
 
rockiroadsCommented:
Sorry but bit confused what u trying to achieve, why you refreshing, and to what u refreshing to

there is always (something along these lines)

currentdb.tabledefs("tablename").connect = "Microsoft Excel Spreadsheet"
currentdb.tabledefa("tablename").SourceTableName = "\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls"
currentdb.tabledefs("tablename").refreshlink



0
 
Karen SchaeferAuthor Commented:
I am refreshing al linked tables that include Oracle, MDB, and XLS types of tables and this is to be done in the middle of the night - using Autoexec.

the File Dialog was just included in the code I copied from The Access Web - website -

Capricorn1 - I am confused by your answer.  Should I add the strIn = Excel 5.0;HDR=YES;IMEX=2;DATABASE=\\Wabelhdk0215892\Intranet Information Server\FSC Ongoing Updated Analyses\NCP DB Market Name Reconcile.xls portion of the code to "fGetLinkedTables" function.

K
0
 
Karen SchaeferAuthor Commented:
rockinroads,

The reason I am refreshing links on a regular basis is to ensure all tables are updated regularly - I am running code to update local tables data with those of the linked tables to create an easy to use data warehouse for my client.

K
0
 
rockiroadsCommented:
but if there linked tables, dont they get updated automatically?
refreshing links is surely just to update connection paths

a linked table looks directly at the remote db's table


if they are not linked, are they imported hence the reason to do the updates?

0
 
Karen SchaeferAuthor Commented:
so you are saying that I don't need to refresh the links with code that they should be good to go via the linked table manager?

Karen
0
 
rockiroadsCommented:
what u said was this

The reason I am refreshing links on a regular basis is to ensure all tables are updated regularly - I am running code to update local tables data with those of the linked tables to create an easy to use data warehouse for my client.



So let me get this straight

You have say two DB's, one is data, the other links to this data?
Or have i got your understanding wrong

I dont know your seutp but refreshing links is for connection, not updating data
if u want to update data, and the table defined is imported or is on a seperate computer, then yes, u need routines to refresh the data

0
 
Karen SchaeferAuthor Commented:
Yes, my database is split and I am refershing the link for connectivity - Once I verify the linked tables have been refreshed the I run code to update the local tablees for my datawarehouse.

Sorry for the confusion - I was just looking to solve the issue of the fReshlink code to handle the xls sheet link.

K
0
 
rockiroadsCommented:
the only time u would need to relink is if you move the location of your backend.
I am assuming when u give out the frontend, it already has linked tables

I have some code here, that given the location of the backend database (this is for MDB), it links the tables

Public Sub RelinkStuff()

    Dim dbData As Database
    Dim tdData As TableDef
    Dim qryData As QueryDef
    Dim sDB As String
    Dim bLink As Boolean
   
   

    On Error Resume Next
   
     'At the moment this is hardcoded to be this DB
    sDB = "C:\EE\EE2.MDB"
    Err.clear
    Set dbData = DBEngine.Workspaces(0).openDatabase(sDB)
    If Err.Number <> 0 Then
        MsgBox "Failed to open database " & sDB & vbCrLf & vbCrLf & Err.Description, vbCritical, "EE"
        Exit Sub
    Else
   
        'Link tables
        For Each tdData In dbData.TableDefs
            If Left(tdData.name, 1) <> "~" And Left(tdData.name, 4) <> "MSys" Then

                'blink used to determine whether we link or not
                'Initialise to false - assume the worst
                bLink = False
                Err.clear

                'Check if table exists
                Debug.Print CurrentDb.TableDefs(tdData.name).name
                If Err.Number > 0 Then
                    'If number is not 3265, it means table does not exist
                    If Err.Number <> 3265 Then
                        MsgBox "Error Checking Local Table : " & tdData.name & vbCrLf & Err.Description
                    Else
                        bLink = True
                    End If
                'Table exists, lets drop it
                Else
                    DoCmd.DeleteObject acTable, tdData.name
                    bLink = True
                End If
                If bLink = True Then DoCmd.TransferDatabase acLink, "Microsoft Access", sDB, acTable, tdData.name, tdData.name
            End If
        Next
   
    End If

End Sub




Now u say u have Oracle databases plus Excel files also?
is the location of excel files likely to change? is your TNS name in Oracle likely to change or are you creating new tables/views?
0
 
rockiroadsCommented:
Sorry for the confusion - I was just looking to solve the issue of the fReshlink code to handle the xls sheet link.


is your excel sheet location likely to change? if your DB is linked directly to that xcel sheet, then

if u make updates to the xcel sheet, it is reflected automatically in the db, since the db is only linking it

if its imported, have a look at deleting local table then reimporting - sample code was given earlier
0
 
Karen SchaeferAuthor Commented:
I have decided to for go the refreshing of the tables on a nightly basis - Since my datasources will not change location the linked tables should be sufficient for now.

Thanks to rockiroads for all his input _ I  am awarding you the points for allyour input.

Thanks,

Karen
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now