Help with VBA Connection to SQL Server

[Sambo8]

Hi There,

Bit lost here I haven't used VB for sooo long. I'm trying to make a connection to SQL Server database, I have several queries that are in MS Query that I would like returned to Excel however, I now want a VB front end so that I can pass 1 Date range to all 7 queries and refresh them all together instead of seperately.

I'm trying to get my connection validated first and below is my code:

Public Sub ConnectToDatabase()



Const sSource As String = "ConnectToDatabase"

Dim IAttempt As Long
Dim sConnectCMPW As String
Dim sConnectBPCS As String
'On Error GoTo ErrorHandler

'Create the connection string to database
sConnectCMPW = "DSN=CAMPUSDEVELOPMENT;UID=sa;PWD=9Hyp68a;Database=CAMPUSDEVELOPMENT"

Set CMPW_Connect = New ADODB.Connection
CMPW_Connect.ConnectionString = sConnectCMPW
CMPW_Connect.Open
CMPW_Connect.Close


End Sub

The Error returned is :

Compile Error user defined type not defined on the following line:

Set CMPW_Connect = New ADODB.Connection

Any help much appreciated.

Many thanks

Sam

 

[LNBruno]

I don't see where you declared CMPW_Connect.

< M!ke >

 

[Sambo8]

Hi Mike,

Apologies if I'm asking dumb questions. I've declared as below:

Public CMPW_Connect As ADODB.Connection

but I still get the same error. What am I doing wrong?

Cheers

Sam

 

[fredericofonseca]

did you add references to MDAC 2.x? (2.7 or greater if possible)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com