[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Refresh Link To Table Once Available

Posted on 2003-12-11
29
Medium Priority
?
2,071 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 13
29 Comments
 
LVL 8

Expert Comment

by:NowaY
ID: 9922141
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
ID: 9922655
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
ID: 9922952
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:NowaY
ID: 9923046
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
ID: 9923090
Yes, tab delimited. Field names at the top / first row.
0
 
LVL 8

Expert Comment

by:NowaY
ID: 9923142
Does the second code section work for you?
0
 

Author Comment

by:tolenal
ID: 9923500
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
ID: 9923568
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
ID: 9923949
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
ID: 9923966
Noway...Almost there.  Please stick with me.
0
 
LVL 8

Expert Comment

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

Expert Comment

by:NowaY
ID: 9924331
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
ID: 9924424
Got it but same error message.  Any thoughts?
0
 
LVL 8

Expert Comment

by:NowaY
ID: 9924510
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
 

Author Comment

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

Expert Comment

by:NowaY
ID: 9924714
junkmailme69-help@yahoo.com

make sure you zip the file up first.
0
 

Author Comment

by:tolenal
ID: 9924762
Just sent it.
0
 
LVL 8

Expert Comment

by:NowaY
ID: 9924803
Got it.
0
 

Author Comment

by:tolenal
ID: 9928286
Any luck?
0
 
LVL 8

Expert Comment

by:NowaY
ID: 9928346
Still hitting it.
0
 

Author Comment

by:tolenal
ID: 9930654
Should I repost the question?
0
 
LVL 8

Expert Comment

by:NowaY
ID: 9930686
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
ID: 9930702
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
ID: 9934667
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
ID: 9934955
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
ID: 9935895
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 2000 total points
ID: 9939017
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 18

Expert Comment

by:Karen Falandays
ID: 9939540
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
ID: 9939563
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

656 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