[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
Re: Linked table dynamic change
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
Re: Linked table dynamic change
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
Re: Linked table dynamic change
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
Re: Linked table dynamic change
Code:
Dim dt1, dt2 As String
I don't think you want OBJECT/STRING. Use DateTime (and convert/cast)
Re: Linked table dynamic change
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