ADOX and linked tables

[racquetballguy]

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(Can cel As Integer)

Dim AppointmentYear As String
Dim AppointmentMont h As String
Dim strGetDataFrom As String

Dim SeeIfMoreThanOn eMonth As Integer

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

With cat
.ActiveConnecti on = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=C:\Docum ents and Settings\Ivan\D esktop\Pernal MD PA\Access Programs\Remind er Notices SK Files.mdb;"
End With

SeeIfMoreThanOn eMonth = DateDiff("m", Forms!frmRemind erDates![Start Date], Forms!frmRemind erDates![End Date])

Select Case SeeIfMoreThanOn eMonth

Case 0
AppointmentYear = Right(DatePart( "yyyy", Forms!frmRemind erDates![End Date]), 2)
AppointmentMont h = DatePart("m", Forms!frmRemind erDates![End Date])

If AppointmentMont h < 10 Then
strGetDataFrom = "01sk0" & AppointmentMont h & AppointmentYear & ".dat"
Else
strGetDataFrom = "01sk" & AppointmentMont h & 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:\Documen ts and Settings\Ivan\D esktop\Pernal MD PA\Access Programs\Remind er Notices SK Files.mdb"
tbl.Properties( "Jet OLEDB:Remote File Name") = strGetDataFrom
End If

Next

Case 1
AppointmentYear = Right(DatePart( "yyyy", Forms!frmRemind erDates![Start Date]), 2)
AppointmentMont h = DatePart("m", Forms!frmRemind erDates![Start Date])

LastDayInMonth = DateAdd("m", 1, DateSerial(Val( AppointmentYear ), Val(Appointment Month), 1)) - 1


If AppointmentMont h < 10 Then
strGetDataFrom = "01sk0" & AppointmentMont h & AppointmentYear & ".dat"
Else
strGetDataFrom = "01sk" & AppointmentMont h & AppointmentYear & ".dat"
End If

Case Else
Debug.Print "Please re-eneter dates"

End Select


End Sub

Thanks
Ivan

[NeoPa]

As mentioned in another thread I wouldn't do it this way at all - but I know you've inherited this.
This is actually quite a knotty problem as various things depend on the table being the same.
Perhaps you could have a dummy linked table that you could update the Connect string for immediately prior to using. It wouldn't work very well with multiple users unless you made it more complicated still with copies of table structures (linked tables still) and unique names etc. That may defeat the whole purpose though so perhaps not a good idea. Single user usage should be ok though.