ADO Connect to SQL

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

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"
 
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
 
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
 
Hi,
See if this helps

Dim param As ADODB.Parameter
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set param = New ADODB.Parameter
Set cmd = New ADODB.Command

cmd.ActiveConnection = cnn
cmd.CommandText = "[dbo].[GetDowntime]"
cmd.CommandType = adCmdStoredProc

Set param = New ADODB.Parameter
param.Name = "YourDate"
param.Type = adDate
param.Value = somedatevalue
param.Direction = adParamInput
cmd.Parameters.Append param

Set rs = cmd.Execute
 
Back
Top