Use ADO to access SQL Database
 

Is it possible to setup an ADO connection to access a SQL 2000 database
from within VBA ?

Thanks


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480835

Use ADO to access SQL Database
 

Silvertip,

Silvertip wrote:
Is it possible to setup an ADO connection to access a SQL 2000
database from within VBA ?

Yes, it's possible. There are a few options, including [Data | Import
External Data -- New Database Query...] and using the ADODB library via
code to execute SQL Server stored procs or run ad-hoc queries. For the
first option, it's pretty straightforward and wizard-based. The second
option will require some learning if you've never done it before. Google
"Excel" and "ADO" to get an idea of how to do this.

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

Use ADO to access SQL Database
 

Silvertip:

Try the following: example:

Remember:

'To use ADO objects in an application add a reference
'to the ADO component. From the VBA window select
'Tools/References< check the box
' "Microsoft ActiveX Data Objects 2.x Library"

Private Sub CommandButton1_Click()

' This declares everying in the procedure

Dim DB_NAME As String
Dim DB_CONNECT_STRING As String
Dim Cnn As ADODB.Connection
Set Cnn = New ADODB.Connection

'Fully quality the path to your database

DB_NAME = ("C:\Program Files\Microsoft Visual Studio\VB98\NWind.mdb")

DB_CONNECT_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DB_NAME & ";" & ", , , adConnectAsync;"

' Open a connection using jet
Cnn.ConnectionString = DB_CONNECT_STRING
Cnn.Open

' Find out if the attempt to connect worked.

If Cnn.State = adStateOpen Then
MsgBox "Welcome to! " & DB_NAME, vbInformation, App_Name

Else
MsgBox "Sorry. No Data today."
End If

Good Luck
TK



"Jake Marx" wrote:

Silvertip,

Silvertip wrote:
Is it possible to setup an ADO connection to access a SQL 2000
database from within VBA ?

Use ADO to access SQL Database
 

aye - use www.connectionstrings.com


--
MattShoreson
------------------------------------------------------------------------
MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
View this thread: http://www.excelforum.com/showthread...hreadid=480835

Use ADO to access SQL Database
 

Here is a good ADO reference...

http://www.erlandsendata.no/english/...php?t=envbadac
--
HTH...

Jim Thomlinson


"Silvertip" wrote:


Is it possible to setup an ADO connection to access a SQL 2000 database
from within VBA ?

Thanks


--
Silvertip
------------------------------------------------------------------------
Silvertip's Profile: http://www.excelforum.com/member.php...o&userid=28456
View this thread: http://www.excelforum.com/showthread...hreadid=480835