Re: Stored Procedure from Access

That code should work as is, with no modifications, in Access.

The variable declarations should be:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sDSN As String
Dim sSQL2 As String

although you could get away with:


Dim objConn As Object
Dim objRS As Object
Dim sDSN As String
Dim sSQL2 As String

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Scott Bailey" <sbailey@xxxxxxxxxxxxxxx> wrote in message
news:uYtiS7vYGHA.3392@xxxxxxxxxxxxxxxxxxxxxxx
i don't want it to prompt for credentials. i just have never used a
connection string within access to access a particular sql server.

Below is some sample asp code that runs a SPROC. I'm just trying to do
something just like this, except from within access.

What would a proper access to sql connection string look like?

ASP CODE ******************

sDSN = "Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=user;Password=pwd;Initial Catalog=myDatabase;Data
Source=192.168.1.999;Use Procedure for Prepare=1;Auto
Translate=True;Packet
Size=4096;"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open sDSN

Set objRS = Server.CreateObject("ADODB.Recordset")

sSQL2 = "MY_SPROC"
objRS.Open sSQL2, objConn



"Dirk Goldgar" <dg@xxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:%23Rz3tEqYGHA.3516@xxxxxxxxxxxxxxxxxxxxxxx
"scott" <sbailey@xxxxxxxxxxxxxxx> wrote in message
news:%23$xf26pYGHA.3704@xxxxxxxxxxxxxxxxxxxx
My problem is that i'm linking to the sql tables.

I don't follow you. When you use a pass-through query or execute a
stored procedure via an ADO connection to the server, it doesn't involve
linked tables at all. You have to specify a suitable connect string for
the query, of course, and for the ADO method you have to provide a
Connection object, which will have to be opened with a suitable connect
string.

can your code be
modified so a server and login credentials be supplied?

I'm not sure what you have in mind. I usually use Windows
Authentication for the SQL Server connection, but if you're not going to
do that, I believe you can pass the credentials as part of the connect
string. Are you talking about having your code prompt the user for the
connect string and credentials, and then building the connect string on
the fly?

can i use the same asp code i use within access to run a sproc?

I don't know what you mean. If you're talking about VBScript code from
an ASP application, then the ADO elements will be the same, though you'd
have to change calls to Server.CreateObject to just CreateObject (for
late binding).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)






.

Relevant Pages

  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ADO connections question
    ... I have a module which creates the ADO connection object to validates the ... Function dbLogin(txtUser As String, txtPword As String, txtServer As String, ... I have a specific login form that calls the dbLogin function as shown below: ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • DTS Transformation Data Task Errors
    ... Dim oIniFile As New IniFile ... Dim sServerName As String = oIniFile.GetString("Source Connection", ... ;ServerName* - String value representing the name or ip address of the ...
    (microsoft.public.sqlserver.dts)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)