[RESOLVED] Linked table dynamic change

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

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

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

Code:

---


  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

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

Code:

---

Dim dt1, dt2 As String

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

---

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

Code:

---

Dim dt1, dt2 As String

---

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

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.

Code:

---

    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