Use ADO to access SQL Database

[Silvertip]

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

Thanks

 

[Jake Marx]

Silvertip,

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]

 

[Guest]

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

 

[MattShoreson]

aye - use www.connectionstrings.com

 

[Guest]

Here is a good ADO reference...

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