calling a function within a sql statement

[ctiwari]

Hi

I was wondering what the best approach to my problem would be. I have two tables table1 and table2. I need to create a third table (say table3) which has fields from both table1 and table2 and also a field that is calculated based on a function. I am using VB .NET and a MS access database.

I understand that one way of doing this would be to create a query that creates a cartesian product of table1 and table2. The SQL statment I use within VB .NET is

Dim qsetCartstr = "SELECT grid.gridid, nevents.neventid, distance([glat], [glon], [nlat], [nlon]) AS ndistance, nevents.nweight FROM grid, nevents"

where distance(...) is the function that is coded within the VB .NET form.

When I execute the code, I get an error saying 'function distance not defined'. If this because the function is not defined within Access? Any help will be greatly appreciated.

Thanks,

Chetan

 

[Wiszh]

Yes, the distance function does not exist in access. You can include other access functions in a query.

The best approach is open a recordset in code and add records to it field by field including the relavent information. With this approach you could add a computed value.

For example:

SET rst = OpenRecordset("tableName&quot
rst.add
rst.field1 = data1
rst.field2 = distance(....)
rst.update

rst.close

 

[ctiwari]

Thanks Wiszh

The reason I want to avoid using a recordset is because I have large volumes of data. The scenario I'm working with involves two tables..

Table1.
Field11 Field12 Field13

Table2
Field21 Field22 Field23

What I need the program to do is loop thru each record of the first table(which may have upto 5000 records) and compare it against every record from the second table (which may have upto 10000 records) and calculate distance,which is a function defined as distance(field11, field12, field21, field22).

When I try using recordsets (or the datareader in .net), the process takes an awfully long time! Someone suggested that I try using a query instead that creates a cartesian product of tables 1 and 2.

I'm not sure what the best method of performing this task would be. Any suggestions will be of great help.

Thanks,

Chetan

 

[Wiszh]

You can use a query to create a temporary table and then use the recordset method to calculate the real third table data. This separates the table linking which is the slowest part. This may improve performace, but I it may not help enough.

Good Luck