Hi
I have data tables in the form of
200612...
200701...
200702...
etc
The user specifies the start date and end date in an input form ie: 12/25/2006 - 1/10/2007.
I want to use access to link to the requested tables but maintain the internal structure already set up in access (relationships, queries, reports, etc). The tables being linked to will change frequently.
My still incomplete code follows below.
The "tbl.Properties("Jet OLEDB:Remote File Name") = strGetDataFrom" stays undefined.
What do I need to add/change to change the Remote File being accessed?
Private Sub Report_Open(Cancel As Integer)
Dim AppointmentYear As String
Dim AppointmentMonth As String
Dim strGetDataFrom As String
Dim SeeIfMoreThanOneMonth As Integer
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
With cat
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb;"
End With
SeeIfMoreThanOneMonth = DateDiff("m", Forms!frmReminderDates![Start Date], Forms!frmReminderDates![End Date])
Select Case SeeIfMoreThanOneMonth
Case 0
AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![End Date]), 2)
AppointmentMonth = DatePart("m", Forms!frmReminderDates![End Date])
If AppointmentMonth < 10 Then
strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
Else
strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
End If
For Each tbl In cat.Tables
If tbl.Name = "tblRecall" Then
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
tbl.Properties("Jet OLEDB:Remote File Name") = strGetDataFrom
End If
Next
Case 1
AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![Start Date]), 2)
AppointmentMonth = DatePart("m", Forms!frmReminderDates![Start Date])
LastDayInMonth = DateAdd("m", 1, DateSerial(Val(AppointmentYear), Val(AppointmentMonth), 1)) - 1
If AppointmentMonth < 10 Then
strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
Else
strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
End If
Case Else
Debug.Print "Please re-eneter dates"
End Select
End Sub
Thanks
Ivan