Re: ADO Connect to SQL
From: Scott (sbailey_at_mileslumber.com)
Date: 05/26/04
- Next message: Lurch: "Referring to other fields in a table from a form"
- Previous message: Douglas J. Steele: "Re: How VBA interprets datetime format"
- In reply to: dan artuso: "Re: ADO Connect to SQL"
- Next in thread: dan artuso: "Re: ADO Connect to SQL"
- Reply: dan artuso: "Re: ADO Connect to SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 26 May 2004 18:41:31 -0500
Connection works My only remaining problem is my SPROC that I want to pass a
single date variable like 5/24/2004 and
return records from 5/24/2004 5:30 AM to 5/25/2004 5:29.59 AM.
FIGURE 1 runs find with datetime hardcoded in the WHERE clause. I need help
building my where clause in FIGURE 2 so the SPROC concatenates the 5:30 AM
and 5:29:59 AM to the variable dtStartDate and filters like FIGURE 1. Any
ideas?
FIGURE 1
CREATE PROCEDURE GetDowntime
(
@dtStartDate datetime
)
AS
BEGIN
SET NOCOUNT ON
SELECT ShiftID AS Shift, downtime AS Downtime
FROM mytable
WHERE (myDateTimefield >= CONVERT(DATETIME, '2004-05-24
05:30:00', 102)) AND
(myDateTimefield <= CONVERT(DATETIME, '2004-05-25
05:29:59', 102))
END
GO
FIGURE 2
WHERE CONVERT (varchar(12), myDateTimefield, 101) >= @dtStartDate
"dan artuso" <dan.artuso@NOSPAMnrc.ca> wrote in message
news:uoXOL%232QEHA.2716@tk2msftngp13.phx.gbl...
> Hi,
> Here are some examples of connection strings and code to open recordsets
>
> Dim cnn as ADODB.Connection
> Dim tvwRsJobs As ADODB.Recordset
> Dim strSql As string
>
> Set cnn = New ADODB.Connection
>
> 'this is if you're authenticating with Windows Authentication
> 'cnn.Open "Provider=sqloledb;" & _
> "Data Source=192.168.100.54;" & _
> "Initial Catalog=DatabaseName;" & _
> "Integrated Security=SSPI;Network Library=dbmssocn"
>
> 'fo'this is for SQL server athentication
> cnn.Open "Provider=sqloledb;" & _
> "Data Source=192.168.100.54;" & _
> "Initial Catalog=DatabaseName;" & _
> "User Id=artusohome;" & _
> "Password=whatever;Network Library=dbmssocn"
>
> 'get the dates of the jobs
> strSql = "Select Distinct DateCreated From Jobs" & strWhere & "
Order By DateCreated"
> tvwRsJobs.Open strSql, cnn, adOpenForwardOnly, adLockOptimistic
>
> --
> HTH
> Dan Artuso, MVP
>
>
> "Scott" <sbailey@mileslumber.com> wrote in message
news:OpsuHE2QEHA.2132@TK2MSFTNGP11.phx.gbl...
> > I have a standard (non access\sqp project) frontend-backend app. Below
is
> > sample code of how I get a recordset from backend.
> >
> > I'd like to know if there is a way with ADO to open a recordset on a SQL
> > Server that isn't linked to my access app, but merely on the network.
I'd
> > prefer to do it without linking to the sql table and just make the
> > connection at runtime via vba code. Can anyone provide sample code?
> >
> > Dim rs As ADODB.Recordset
> > Set rs = New ADODB.Recordset
> >
> > rs.ActiveConnection = CurrentProject.Connection
> > rs.CursorType = adOpenForwardOnly
> > rs.LockType = adLockReadOnly
> >
> > rs.Open "SELECT ShiftName from mytable"
> >
> >
>
>
- Next message: Lurch: "Referring to other fields in a table from a form"
- Previous message: Douglas J. Steele: "Re: How VBA interprets datetime format"
- In reply to: dan artuso: "Re: ADO Connect to SQL"
- Next in thread: dan artuso: "Re: ADO Connect to SQL"
- Reply: dan artuso: "Re: ADO Connect to SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
