By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,742 Members | 1,570 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,742 IT Pros & Developers. It's quick & easy.

ADOX and linked tables

P: 20
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
Dec 29 '06 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,426
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.
Dec 31 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.