adodb connection string for SQL Server

Does anybody have the connection string for ADODB to connect to SQL Server.

What I have so far is
strConnect = "Provider=SQLOLEDB;
Data source= I am not sure if this is the server name;
Initial Catalog = Name of the database;
User Id = Is this supposed to be sa or sysadm;
Password= mypassword;

thanks for your help
Joel

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

kym-foster

Joel,

This is what I have in my VB2010 code to connect to SQL Server. I ran into issues trying to perform multiple selects at the same time, so that's what the last parameter is for.

sConn = "Data Source=1.1.1.1;Integrated Security=False;database=databasename;User ID=myUserid;Password=myPassword;MultipleActiveResultSets=True"

Good luck,
Carol

Legacy Contributor

Data source = server name or named instance
IE from my sql.ini file for visual
VMFG=MYSERVER,VMFG
Yields
Data Source = MYSERVER
Initial Catalog = VMFG

User ID and Password can be any legitimate user/pwd combo on the SQL server with approriate database access. I personally use a logon script if I am using a sysadm level function rather than store credentials. I use the minimum level of access necessary to acheive results (IE I don't use sysadm for read only functions...)

Inquiry Tab Placement.docx

Legacy Contributor

BTW what is your programming environment?

Legacy Contributor

new server 2008 with SQL Server 2008 R2, XP pro and couple of windows 7 desktops. All my programming has been with SQLBase and now we are converting to SQL Server. I do have a System DSN that I have connected to the SQL Server and it works fine. I use it for some Access reports.

I am now getting this error.
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

When I close my error window it comes up with another window with the error "Runtime Error "424" Object required

here is my connection that I am using.

Public VMFG_Connect As New ADODB.Connection
Public ConnectString As String

ConnectString = "Provider=SQLOLEDB;Data Source="Name of SQL Server";Integrated Security=FALSE;database=NEWDB;User Id=SYSADM;Password=My Sysadm password"

Set VMFG_Connect = New ADODB.Connection
VMFG_Connect.Open ConnectString

I have also tried on the 2 lines above

Set VMFG_Connect = CreateObject("ADODB.Connection")
VMFG_Connect.Open ConnectString

Thanks for all your help

Joel

Legacy Contributor

from vba in excel I use

Function getOneTier() As ADODB.Recordset
Dim cnt As ADODB.Connection
Dim stSQL As String
Dim stADO As String
stADO = "Provider=SQLOLEDB;Data Source=" & Range("DataSource") & ";Initial Catalog=" & Range("InitCat")
stSQL = "SELECT STUFF FROM STUFF_TABLE"
Set cnt = New ADODB.Connection

With cnt
.CursorLocation = adUseClient
.Open stADO, UCase(Range("USER_NAME")), Range("PASSWD")
.CommandTimeout = 0
Set getOneTier = .Execute(stSQL)
End With
Set cnt = Nothing

End Function

This is a function that returns a recordset. The values for Server,Database, User and PWD come from login form and are stored temporarily in named ranges....

I have a variety of methods I use in .NET based on:
Imports System.Data.SqlClient

Legacy Contributor

TRY
Public VMFG_Connect As ADODB.Connection (NOT NEW)

Wichtige Lösungen in der Wissensdatenbank April 2013.pdf

Legacy Contributor

Just to clarify...

Public VMFG_Connect As ADODB.Connection

OR:

Dim VMFG_Connect As ADODB.Connection

Legacy Contributor

This one works for me (Excel VBA):

Dim strConn As String
strConn = "Driver={SQL Server};Server=EnterVisualServerNameHere; Database=" & SourceDBName & ";Uid=EnterUseID; Pwd=EnterUserPassword;"

[Updated on 6/2/2011 1:56 PM]

Legacy Contributor

Ok,
1) Removed the NEW from Public VMFG_Connect As ADODB.Connection
and get the same error.
2) Put in Driver={SQL Server} instead of Provider=SQLOLEDB and get the same error.
3) Changed SYSADM to SA and get the same error.
4) Changed User Id and Password to Uid and Pwd and still same error.
5) tried different variations of these and still same error.

Legacy Contributor

Does your SQL statement work in SQL Management Studio?
Have you tried a simple Simple SQL like "Select top 1 description from part"

Googling your initial error indicates possible SQL statement error or type issue (NULL instead of Empty String)

What are you programming? Macro Script? Excel/Access VBA? VB.NET? ASP?

Legacy Contributor

from my excel example simplified

DIM getRS As ADODB.Recordset
Dim cnt As ADODB.Connection
Dim stSQL As String
Dim stADOConnect As String
stADOConnect = "Provider=SQLOLEDB;Data Source=SERVER ;Initial Catalog=VMFG"
stSQL = "Select top 1 description from part"
Set cnt = New ADODB.Connection

cnt.CursorLocation = adUseClient
cnt.Open stADO, "USER","PASSWD"
cnt.CommandTimeout = 0
Set getRS = cnt.Execute(stSQL)
Set cnt = Nothing
'*******Use getRS recordset here*******
Set getRS=nothing

Legacy Contributor

sorry should be

DIM getRS As ADODB.Recordset
Dim cnt As ADODB.Connection
Dim stSQL As String
Dim stADOConnect As String
stADOConnect = "Provider=SQLOLEDB;Data Source=SERVER ;Initial Catalog=VMFG"
stSQL = "Select top 1 description from part"
Set cnt = New ADODB.Connection

cnt.CursorLocation = adUseClient
cnt.Open stADOConnect, "USER","PASSWD"
cnt.CommandTimeout = 0
Set getRS = cnt.Execute(stSQL)
Set cnt = Nothing
'*******Use getRS recordset here*******
Set getRS=nothing

Legacy Contributor

I am using VB6 programming. I have several Access reports that work just fine using a System DSN. The problem is I can not even open a connection to the database to do a sql script. This is just trying to open the main connection to the database. As soon as it hits the line to open the connection --> "VMFG_Connect.Open ConnectString" it goes to my error routine and outputs the error. I do notice an extra line in yours "cnt.CursorLocation = adUseClient" do you know what this line is for??

Thanks
Joel

Legacy Contributor

Use cursor defined by client dll file
see: msdn.microsoft.com/.../ms677542(v=vs.85).aspx

Did you try the code I sent, just as it is replacing SERVER an etc? I think it is pretty much VB6 compatible... It works fine in Excel with msado15.dll referenced....

Also look here: (a simpler connection string may be in order)
social.msdn.microsoft.com/.../1a2d2a6a-a3c1-47bf-8997-71efbb5ba222

Or maybe here:
support.microsoft.com/.../269495

Legacy Contributor

Been EXTREMELY busy lately and finely have to to finish this. I went back in and deleted the whole string and started over. I put in the first parts and started building on that and it worked. Don't know for sure what was wrong.

Thanks for all your help.

Now I am working on getting the COM objects to work and trying to write some macros. will start a new thread.

Thanks
Joel

rm_benefit_administration_aca_training.pptx

Legacy Contributor

Hi
Check the following
Connection string for SQL Server Managed Security
without saving password
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=DataBase;Data Source=SQLServer

save password
Provider=SQLOLEDB.1;Password=saPwd;Persist Security Info=True;User ID=sa;Initial Catalog=DataBase;Data Source=SQLServer

Connection string for Windows Managed Security

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=DataBase;Data Source=SQLServer

DSN connection string
Set cn = New ADODB.Connection

cn.Open "DSN=DSNName", "sa", "saPwd"
cn.DefaultDatabase = "DataBaseName"
I hope you serve something
lucky

Copyright © 2025 Infor. All rights reserved.