Solved

Refresh Link To Table Once Available

Posted on 2003-12-11
29
2,060 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:tolenal
  • 15
  • 13
29 Comments
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
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
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
Yes, tab delimited. Field names at the top / first row.
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
Does the second code section work for you?
0
 

Author Comment

by:tolenal
Comment Utility
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
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
Noway...Almost there.  Please stick with me.
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
I'm still here.. hang on. Update comming.
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
Got it but same error message.  Any thoughts?
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:tolenal
Comment Utility
Great idea.  How do I find your e-mail address?
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
junkmailme69-help@yahoo.com

make sure you zip the file up first.
0
 

Author Comment

by:tolenal
Comment Utility
Just sent it.
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
Got it.
0
 

Author Comment

by:tolenal
Comment Utility
Any luck?
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
Still hitting it.
0
 

Author Comment

by:tolenal
Comment Utility
Should I repost the question?
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
That's cool.  I was on the road most of the day and have not focused.  See you then.
0
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
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
 

Author Comment

by:tolenal
Comment Utility
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
 
LVL 8

Accepted Solution

by:
NowaY earned 500 total points
Comment Utility
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
 
LVL 17

Expert Comment

by:Karen Falandays
Comment Utility
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
 
LVL 8

Expert Comment

by:NowaY
Comment Utility
Thanks again. As I said before. Didn't realize. I've re-read the rules and will comply in the future. Thanks again!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now