[RESOLVED] Linked table dynamic change

[makdu]

Hi all,
I have an MS access database with some .dbf files linked to it. One of the dbf file is a sales file. I am trying to generate a report for each of the month's sales. For that the sales dbf file will be different for each of the month. I want to give an option to the user to specify the file name from which he wants to use to generate the report. After accepting the input from the user, i need to link to that table in MSACCESS. Is there a way to achieve this from vb.net

[makdu]

Hi,
i want to link an externel table to an ms access database through vb.net. Is this possible. I am able to create a new database and a new table, but not linking with external table. Any help will be appriciated

[makdu]

Hi,
I changed my approach to use the foxpro table direclty using VFOLEDB connection. Some of the select statemnts are working fine, but some are not in this case. For example

Public fcn As ADODB.Connection
fcn = New ADODB.Connection
fcn.ConnectionString = "provider=VFPOLEDB.1;Data Source=" & tablefolderpath
fcn.Open()

rst = New ADODB.Recordset

rst.Open(" SELECT DISTINCT(ITEM) FROM " & filename & " WHERE (DATE >= #" & dt1 & "# AND DATE <= #" & dt2 & "#) AND SOLD_AT = '" & shname & "' AND CATEGORY LIKE '" & catval & "' AND BRAND = '" & br_name & "' GROUP BY ITEM ", fcn)

i am getting missing operand error. Is this something to do with the select statment i wrote . This select statment works fine with OLEDB connection

[makdu]

I have narrow downed the problem further. The problem is with the Where date>= part. . I removed the date part from the statment and the statement is getting executed properly. Whats wrong with

Dim dt1, dt2 As String

WHERE (DATE >= #" & dt1 & "# AND DATE <= #" & dt2 & "#)

As mentioned earlier, the above where statment is working fine with OLEDB connection

[dglienna]

Dim dt1, dt2 As String


I don't think you want OBJECT/STRING. Use DateTime (and convert/cast)

[makdu]

I found out the problem . When we are using the Foxpro table using vfpoled connection, the date has to be specified in "{^yyyy/mm/dd} format.
So here is the solution for me.

dt1 = d1.ToString("yyyy/MM/dd")
dt2 = d2.ToString("yyyy/MM/dd")

rst.Open("SELECT DISTINCT(ITEM) FROM " & salefilename & " WHERE (DATE >= {^" & dt1 & "} AND DATE <= {^" & dt2 & "}) AND SOLD_AT = '" & shname & "' AND CATEGORY LIKE '" & catval & "' AND BRAND = '" & br_name & "' GROUP BY ITEM ", fcn)


Now i am able to get the distinct item from the table