MSDE and ADO

When i call ADOConnection.Connected := False
The connection with the MSDE server is still opened, why that?
I want to close that connection with the server
The connection is only closed when my application is terminated
Im using MSDE 2000 and my application is writen in Delphi
Connection pooling is the probably cause. See this article:
http://msdn.microsoft.com/library/de...l/pooling2.asp
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Marcos Lommez" <lommez@.hotmail.com> wrote in message
news:O18zpcbUGHA.5108@.TK2MSFTNGP09.phx.gbl...
> When i call ADOConnection.Connected := False
> The connection with the MSDE server is still opened, why that?
> I want to close that connection with the server
> The connection is only closed when my application is terminated
> Im using MSDE 2000 and my application is writen in Delphi
>
>
|||Thanks
I have found the solution in that article
I just need to put the ';OLE DB Services = -2' to the end of my connetion
string
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> escreveu na mensagem
news:%23Bd2jedUGHA.736@.TK2MSFTNGP12.phx.gbl...
> Connection pooling is the probably cause. See this article:
>
http://msdn.microsoft.com/library/de...l/pooling2.asp
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Marcos Lommez" <lommez@.hotmail.com> wrote in message
> news:O18zpcbUGHA.5108@.TK2MSFTNGP09.phx.gbl...
>
sql

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
>

MSDE & Web pages

Hello all,
I am trying to connect a web page to an Access database (using ADO), using
the MSDE engine as opposed to jet. What I have at the moment doesn't work,
could anyone help?
Thanks in advance,
Adonis.I'm confused by your posting. Are you using MSDE or Access -- two different
databases. And, what do you have as a connection string ?
Michael D.|||Sorry about that. Iam using MSDE. (I have upsized an Mdb file up to an
adp/mdf). The connection string is
strDBPath=server.MapPath("quaSAR.adp")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=msdasql;Data Source=" &strDBPath & ";"
Any ideas?
Regards,
Adonis.
"Michael D" <anonymous@.discussions.microsoft.com> wrote in message
news:AC684267-206E-47B6-A607-9E9230771A91@.microsoft.com...
> I'm confused by your posting. Are you using MSDE or Access -- two
different databases. And, what do you have as a connection string ?
> Michael D.|||(1) What error message do you get when you are trying to connect ? Please cu
t an paste the entire message here.
(2) Can you connect to the MSDE instance via Query Analyser or an ODBC DSN c
onnection ?
Thanks,
Michael D.|||Have a look at this article...
http://support.microsoft.com/defaul...&product=adonet
Michael D.

MSDE & Web pages

Hello all,
I am trying to connect a web page to an Access database (using ADO), using
the MSDE engine as opposed to jet. What I have at the moment doesn't work,
could anyone help?
Thanks in advance,
Adonis.
I'm confused by your posting. Are you using MSDE or Access -- two different databases. And, what do you have as a connection string ?
Michael D.
|||Sorry about that. Iam using MSDE. (I have upsized an Mdb file up to an
adp/mdf). The connection string is
strDBPath=server.MapPath("quaSAR.adp")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=msdasql;Data Source=" &strDBPath & ";"
Any ideas?
Regards,
Adonis.
"Michael D" <anonymous@.discussions.microsoft.com> wrote in message
news:AC684267-206E-47B6-A607-9E9230771A91@.microsoft.com...
> I'm confused by your posting. Are you using MSDE or Access -- two
different databases. And, what do you have as a connection string ?
> Michael D.
|||(1) What error message do you get when you are trying to connect ? Please cut an paste the entire message here.
(2) Can you connect to the MSDE instance via Query Analyser or an ODBC DSN connection ?
Thanks,
Michael D.
|||Have a look at this article...
http://support.microsoft.com/default...product=adonet
Michael D.

MSDE & Web pages

Hello all,
I am trying to connect a web page to an Access database (using ADO), using
the MSDE engine as opposed to jet. What I have at the moment doesn't work,
could anyone help?
Thanks in advance,
Adonis.Sorry about that. Iam using MSDE. (I have upsized an Mdb file up to an
adp/mdf). The connection string is
strDBPath=server.MapPath("quaSAR.adp")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=msdasql;Data Source=" &strDBPath & ";"
Any ideas?
Regards,
Adonis.
"Michael D" <anonymous@.discussions.microsoft.com> wrote in message
news:AC684267-206E-47B6-A607-9E9230771A91@.microsoft.com...
> I'm confused by your posting. Are you using MSDE or Access -- two
different databases. And, what do you have as a connection string ?
> Michael D.|||(1) What error message do you get when you are trying to connect ? Please cut an paste the entire message here
(2) Can you connect to the MSDE instance via Query Analyser or an ODBC DSN connection
Thanks
Michael D.|||Have a look at this article..
http://support.microsoft.com/default.aspx?kbid=308656&product=adone
Michael D