Question 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

I have tried to create a new database in msaccess. I am able to do that. But linking a file is not getting happen. I can create a new table even in the ms acess from vb.net.
What is the procedure to link an external table to the ms access. Is this possible ?

 

[Hack]

You can load all your tables into a DataSet and then create Relations between tables.

I would suggest using a dataset which in .NET, is simply a collection of DataTable. These datatables can be any type of database tables regardless of where the original data came from.

 

[makdu]

Missing Operand message

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

 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 have found the problem and 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 & "#)

i am getting missing operand error.

 

[makdu]

Missing Operand message

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

 

[Hack]

So, does this mean your issue is resolved?

 

[makdu]

Yes, its resolved