Programming Help Needed-- Prompt User to locate linked table 1

[jprochelle]

I am trying to create a form that prompts the user to locate a linked file. Possibly a macro that leads to the linked table manager file, automatically checks off "prompt for location" and clicks "OK" so that the only thing the user does is find the file(s).

I guess the outcome will be for the user to say where the file is, and then Access will temporarily store the data for use in a report.

Is this too complex? I hope its possible because it would make my life easy.

Thanks!

If anyone wants to really help me, I can send the file and that might help.

 

[denosaur]

does the file that you want to link to have a dynamic name/location? if not, why do you want to prompt the user to find it?

 

[jprochelle]

It is a revenue reporting database and the reports are in EXCEL. I want to be able to get the report data on a monthly basis--which is why the tables are linked the database. The problem is that with each new month, the data changes, and though I have saved the report as "MonthlyRevenues" for now, I want the user to be able to tell Access where to get the new report so that it can read the data.

Basically, the data changes every month and when I create capabilities for the database to compare changes month to month, then I will need to look at different tables.

Perhaps there's a way for Access to temporarily store the table just for the reports purpose--but I'm not sure how to do this.

Thanks for all of your help. Again I have the database if anyone needs a "closer look".

 

[denosaur]

a closer look might be helpful. i have several ideas that might help. is there a standard naming convention and location for the monthly excel files that you need to look up? in other words are the files always in the same directory and do they have a similar names from month to month (ie. revenues0504.xls and revenues0604.xls for May and June files)? Do you generally compare one month to the previous month or do you compare randomly selected months? just trying to get a feel for your requirements. assuming that you have some standardization, i am offering the following code for you to consider. it is based on my comments above. you may find it useful. if there is currently no standard naming conventions, you may consider forcing some standards to simplify your job. -my extra two cents.

'------------------------------------------------------------
' This procedure assumes that the Current Month is actually
' the previous month and the Previous Month is actually the
' month before that. Example: In July, you report using June
' and May data.
'------------------------------------------------------------
Function LinkMonthlyReveneues()
On Error GoTo LinkMonthlyReveneues_Err

    Dim strPath As String
    Dim strCurMon As String
    Dim strPreMon As String
    Dim strCurMonRevFile As String
    Dim strPreMonRevFile As String

    strPath = "c:\temp\"
    strCurMon = Format(DateSerial(Year(Date), Month(Date) - 1, Day(Date)), "mmyy")
    strPreMon = Format(DateSerial(Year(Date), Month(Date) - 2, Day(Date)), "mmyy")
    strCurMonRevFile = "revenues" & strCurMon
    strPreMonRevFile = "revenues" & strPreMon
    
    ' Run the DeleteMonthlyRevenues procedure
    DeleteMonthlyRevenues
    
    ' Link to Current Month's Revenues Spreadsheet
    DoCmd.TransferSpreadsheet acLink, 8, "tblMonthlyRevenuesCurrent", strPath & strCurMonRevFile, True, ""
    ' Link to Previous Month's Revenues Spreadsheet
    DoCmd.TransferSpreadsheet acLink, 8, "tblMonthlyRevenuesPrevious", strPath & strPreMonRevFile, True, ""

LinkMonthlyReveneues_Exit:
    Exit Function

LinkMonthlyReveneues_Err:
    MsgBox Error$
    Resume LinkMonthlyReveneues_Exit

End Function

'------------------------------------------------------------
' This procedure destroys the links to two spreadsheets, to
' prepare to refresh the links with the LinkMonthlyRevenues
' procedure.
'------------------------------------------------------------
Function DeleteMonthlyRevenues()
On Error GoTo DeleteMonthlyRevenues_Err

    ' Delete the tblMonthlyRevenuesCurrent table link
    DoCmd.DeleteObject acTable, "tblMonthlyRevenuesCurrent"
    ' Delete the tblMonthlyRevenuesPrevious table link
    DoCmd.DeleteObject acTable, "tblMonthlyRevenuesPrevious"

DeleteMonthlyRevenues_Exit:
    Exit Function

DeleteMonthlyRevenues_Err:
    MsgBox Error$
    Resume DeleteMonthlyRevenues_Exit

End Function

i hope this helps.