Refresh Link To Table Once Available

I have an Access database that has external links to a network drive where a .txt file is posted weekly with sales information when they become available.  The filename of the .txt files are related to the week of the fiscal year (ex. report_200444 for week 44 of 2004 then report_200445 for week 45 of 2004).  I have separate databases for each quarter in a fiscal year (weeks 1-13 for Q1, weeks 13-26 for Q2, weeks 27-39 for Q3, weeks 40-52 for Q4).  I have developed queries that looks at the weekly reports (place holder with dummy data) with the same report names.

In the past I have refreshed the table links manually when the week of sales information become available making the query valid.  Now I need VBA code that will look to a table of report names that I establish and check the network if the report is available then deletes the place holder with the same name and creates a linked table.
tolenalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NowaYCommented:
Check to see if file exists:

' connect to db and open RS

Sub FileExists()
Dim fso
Dim file As String
file = ""&  rs!ReportName  &""
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there    
Else
    Call CreateNewTableLink
End If
End Sub

Do you want more detail or will this do?
0
tolenalAuthor Commented:
Need more detail.

Let's say that I have a table called WEEKS with three entries:  week_200444.txt, week_200445.txt, week_200446.txt.

I have one linked table called week_200444 and two tables (week_200445 and week_200446) with dummy information but the same field names as week_200444.  The sales information for week_200445 has just become available.

1.  I need VBA code that will check the WEEKS table and check a network drive d:\network\source for the existance of the files.
2.  If the files exists, the VBA code will delete the table with the dummy information and create a linked table to the new .txt file.
3.  The linked table needs to have the same fields as the .txt file.  I don't have and scripts for reading in information such as the one you are calling in CreateNewTableLink.

 names as
0
NowaYCommented:
OK, what do the text files look like? Are the delimited in some way? Do they contain field names at the top?



Sub FileExists()
Dim fso
Dim file As String
file = ""&  rs!ReportName  &""
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there    
Else

  DoCmd.DeleteObject acTable, "" & oldtablename & ""
DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & filenamevar & "", True


End If
End Sub

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NowaYCommented:
DISREGARD LAST COMMENT. I hit submit before I was finished.

put this in the onclick event of a command button that will be used to check if there is a new week file available:

  Set rs = thisDB.OpenRecordset _
   ("select * from Weeks", dbOpenDynaset)

OldWeekName = week_200444 'starting week name
 
While rs.EOF = False
     Call FileExits(rs!WeekName, OldWeekName)
   
     OldWeekName= rs!WeekName ' this is the name like week_200444

wend


Now here is the sub that does the action:


Sub FileExists(oldtablename as String, tablenamevar as String)
Dim fso
Dim file As String
file = ""&  filenamevar  &".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do noth    
Else

  DoCmd.DeleteObject acTable, "" & oldtablename & ""
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True

End If
End Sub

0
tolenalAuthor Commented:
Yes, tab delimited. Field names at the top / first row.
0
NowaYCommented:
Does the second code section work for you?
0
tolenalAuthor Commented:
I tried it but was getting "ByRef argument type mismatch".  I also change "FileExits" to "FileExists".  Question: should there be quotes around "week_200444" for OldWeekName.  Where do I declare the network drive location to look and compare?
0
NowaYCommented:
Set rs = thisDB.OpenRecordset _
   ("select * from Weeks", dbOpenDynaset)

OldWeekName = "week_200444"
   
While rs.EOF = False
     FileExists(rs!WeekName, OldWeekName)
     OldWeekName= rs!WeekName ' this is the name like week_200444
wend

Sub FileExists(ByVal oldtablename as String, ByVal tablenamevar as String)
Dim fso
Dim file As String
file = "d:\network\source\"&  filenamevar  &".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do nothing    
Else
  DoCmd.DeleteObject acTable, "" & oldtablename & ""
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True
End If
End Sub
0
tolenalAuthor Commented:
I'm getting the following on FileExists(rs!WeekName, OldWeekName).

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Syntax error
---------------------------
OK   Help  
---------------------------

Just to reiterate:

table - Weeks
field - Weekname
entries: week_200444, week_200445, week_200446

linked table - week_200444
tables with dummy data to be deleted: week_200445, week_200446

in local subdirectory c:\Source with week_200444.txt and week_200445.txt

The test should delete the table with dummy data week_200445 because week_200445.txt was just posted to the local subdirectory.

Here is a copy paste of my command

Option Compare Database




Private Sub Command3_Click()

Set rs = thisDB.OpenRecordset _
   ("select * from Weeks", dbOpenDynaset)

OldWeekName = "week_200444"
   
While rs.EOF = False
     FileExists(rs!WeekName, OldWeekName)
     OldWeekName = rs!WeekName ' this is the name like week_200444
Wend


End Sub

Sub FileExists(ByVal oldtablename As String, ByVal tablenamevar As String)
Dim fso
Dim file As String
file = "c:\Source\" & filenamevar & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do nothing
Else
  DoCmd.DeleteObject acTable, "" & oldtablename & ""
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True
End If
End Sub



0
tolenalAuthor Commented:
Noway...Almost there.  Please stick with me.
0
NowaYCommented:
I'm still here.. hang on. Update comming.
0
NowaYCommented:
Private Sub Command3_Click()

Dim NewWeekName as String
Dim OldWeekName as String

Set rs = thisDB.OpenRecordset _
   ("select * from Weeks", dbOpenDynaset)

OldWeekName = "week_200444"
   
While rs.EOF = False
     NewWeekName = rs!WeekName
     FileExists(OldWeekName, NewWeekName)
     OldWeekName = rs!WeekName
Wend

End Sub

Sub FileExists(ByVal oldtablename As String, ByVal tablenamevar As String)
Dim fso
Dim file As String
file = "c:\Source\" & filenamevar & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do nothing
Else
  DoCmd.DeleteObject acTable, "" & oldtablename & ""
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True
End If
End Sub
0
tolenalAuthor Commented:
Got it but same error message.  Any thoughts?
0
NowaYCommented:
I need to know the exact error message:


Private Sub Command3_Click()

Dim NewWeekName as String
Dim OldWeekName as String

on Error goto HandleError

Set rs = thisDB.OpenRecordset _
   ("select * from Weeks", dbOpenDynaset)

OldWeekName = "week_200444"
   
While rs.EOF = False
     NewWeekName = rs!WeekName
     FileExists(OldWeekName, NewWeekName)
     OldWeekName = rs!WeekName
Wend


HandleError:

msgbox err.Number & vbcr & vbcr & err.description

End Sub


Sub FileExists(ByVal oldtablename As String, ByVal tablenamevar As String)
Dim fso
Dim file As String
file = "c:\Source\" & filenamevar & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do nothing
Else
  DoCmd.DeleteObject acTable, "" & oldtablename & ""
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True
End If
End Sub




Can you send me the DB file? This would go much quicker.
0
tolenalAuthor Commented:
Great idea.  How do I find your e-mail address?
0
NowaYCommented:
junkmailme69-help@yahoo.com

make sure you zip the file up first.
0
tolenalAuthor Commented:
Just sent it.
0
NowaYCommented:
Got it.
0
tolenalAuthor Commented:
Any luck?
0
NowaYCommented:
Still hitting it.
0
tolenalAuthor Commented:
Should I repost the question?
0
NowaYCommented:
Sorry I haven't gotten back to you yet. I'm leaving work here in about an hour. I'll finish it when I get home. Is that OK?
0
tolenalAuthor Commented:
That's cool.  I was on the road most of the day and have not focused.  See you then.
0
NowaYCommented:
Just to let you know, I'm still working on this. I'm sorry it has taken so long but my free time has been minimal the last few days.
0
tolenalAuthor Commented:
Got your note.  The weekly report that we are working on will hit the network Monday late morning.  I need to get a solution to my boss by then or shortly after.  I hope to wrap up or repost by Monday.  I know you are doing your best.  Thanks in advance.
0
tolenalAuthor Commented:
I did some changes to the coded and used MS DAO 3.6 Object Library.  It is looping on the If Then without doing what it is supposed to do.  Please check and see if you have any ideas.

Option Compare Database

Private Sub Command3_Click()

Dim NewWeekName As String
Dim OldWeekName As String
Dim rs As DAO.Recordset

On Error GoTo HandleError

Set rs = CurrentDb.OpenRecordset _
   ("select Weekname from Weeks", dbOpenDynaset)

OldWeekName = "week_200444"
   
While rs.EOF = False
     NewWeekName = rs!WeekName
     Call FileCHECKExists(OldWeekName, NewWeekName)
     OldWeekName = rs!WeekName
Wend


HandleError:

MsgBox Err.Number & vbCr & vbCr & Err.Description

End Sub


Sub FileCHECKExists(oldtablename As String, tablenamevar As String)
Dim fso As Variant
Dim file As String
file = "c:\Source\" & filenamevar & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do nothing
Else
  DoCmd.DeleteObject acTable, "" & oldtablename & ""
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True
End If
End Sub

0
NowaYCommented:
Not sure if you have the most up-to-date code:

Private Sub Command3_Click()

If Combo5.Value = "" Then
    MsgBox "Please select a start week."
    Exit Sub
End If


Dim NewWeekName As String
Dim OldWeekName As String

On Error GoTo HandleError

Dim RS As DAO.Recordset

Set RS = CurrentDb.OpenRecordset _
   ("select * from Weeks", dbOpenDynaset)

OldWeekName = Combo5.Value
   
While RS.EOF = False
     NewWeekName = RS!WeekName
     Call FileExists(OldWeekName, NewWeekName)
     OldWeekName = RS!WeekName
     RS.MoveNext
    If RS.EOF = True Then Exit Sub
Wend

HandleError:

MsgBox Err.Number & vbCr & vbCr & Err.Description

End Sub


Sub FileExists(ByVal oldtablename As String, ByVal tablenamevar As String)

Dim fso
Dim file As String
file = "c:\Source\" & tablenamevar & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
  ' file not there
  ' do nothing
Else
 
  If oldtablename <> "" Then
    DoCmd.DeleteObject acTable, "" & oldtablename & ""
  End If
 
  DoCmd.TransferText acLinkDelim, , "" & tablenamevar & "", "" & tablenamevar & ".txt", True
End If
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen FalandaysTraining SpecialistCommented:
Hey noway, this is not ok. Do not give out email address directly. Pls re-read the ee rules. Moderator might kick you off.
kfalandays
ee cleanup volunteer
0
NowaYCommented:
Thanks again. As I said before. Didn't realize. I've re-read the rules and will comply in the future. Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.