MSDE 2000A inner join problems

Hi All
I am using VB6 and MSDE 2000
I previously used Access 2000 and used the following ado sql to create a
temp table in access
(Invoice table contains Invoice data such as Invoice number, date etc.
Idetail table contains details of the invoice individual items e.g product
code, Invoice Number etc) The link is the Invoice Number field in each table
(Master/Detail)
sql = "SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp IN '" & App.path & "\data\tramcarstemp.mdb'
FROM idetail inner join [invoice] on idetail.inv_num = invoice.inv_num where
invoice.inv_date between #" & Format(DT1.Value, "m-d-yyyy") & "# and #" &
Format(DT2.Value, "m-d-yyyy") & "#"
cn.execute sql
This worked fine in Access 2000
I am upgrading to MSDE2000 and using the following code in TSql
Private Sub maketable()
Dim a, fso As New FileSystemObject
Dim x As Integer
Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'true'")
a.WriteLine ("GO")
a.WriteLine ("USE " & "Tramcars")
a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp")
a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
invoice.inv_num")
a.WriteLine ("where invoice.inv_date between '" &
Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
a.WriteLine ("GO")
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'false'")
a.WriteLine ("GO")
a.Close
Set a = Nothing
End Sub
The table is created but the inserted invoice dates are only ever 1 of 2
dates
Also when I read from the table to graph the data with
sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
[prod_code] = '" &
frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0) & "'
group by [inv_date]"
rs.Open sql, cn, adOpenDynamic, adLockReadOnly
There are several records in the recordset but calling rs.recordcount = -1,
which didn't happen in Access 2K
Any ideas
Steve
hi Steve,
steve wrote:
> Hi All
> ...
> Private Sub maketable()
> Dim a, fso As New FileSystemObject
> Dim x As Integer
> Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'true'") a.WriteLine ("GO")
> a.WriteLine ("USE " & "Tramcars")
> a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
> idetail.price INTO idetailtemp")
> a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
> invoice.inv_num")
> a.WriteLine ("where invoice.inv_date between '" &
> Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
> Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
> a.WriteLine ("GO")
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'false'") a.WriteLine ("GO")
> a.Close
>
> The table is created but the inserted invoice dates are only ever 1
> of 2 dates
please use the ISO format when referencing dates... dates can be confusing,
but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD' format you will
be out of troubles.. please have a look at
http://www.windowsitpro.com/SQLServe...9147/9147.html ,
http://www.karaszi.com/SQLServer/info_datetime.asp for further info..

> Also when I read from the table to graph the data with
> sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
> [prod_code] = '" &
> frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0)
> & "' group by [inv_date]"
> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
> There are several records in the recordset but calling rs.recordcount
> = -1, which didn't happen in Access 2K
this depends on the MDAC recordset type... the recordcount value will be
available when the recordset has been fully populated, that's to say as soon
as you perform a .MoveLast operation.. (BTW... .Movelast can be time
consuming as all rows must be fetched and transferred)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Thanks. Worked a treat
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3k76maFt35b8U1@.individual.net...
> hi Steve,
> steve wrote:
> please use the ISO format when referencing dates... dates can be
> confusing, but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD'
> format you will be out of troubles.. please have a look at
> http://www.windowsitpro.com/SQLServe...9147/9147.html ,
> http://www.karaszi.com/SQLServer/info_datetime.asp for further info..
>
> this depends on the MDAC recordset type... the recordcount value will be
> available when the recordset has been fully populated, that's to say as
> soon as you perform a .MoveLast operation.. (BTW... .Movelast can be time
> consuming as all rows must be fetched and transferred)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>