create a connection to a SQL Server 7 database

[tyo]

i'm very new here, trying to create a connection to a sql server 7 database in excel vba, this is the code i just wrote, but doesn't work, thanks in avance for any help!

sub Query

Dim wrkODBC As workspace
Set wrkODBC = CreateWorkspace("ODBCworspace", "", _
"", dbUseODBC) 'set the variable to create a workspace with the name of ODBCworkspace, username, pwd, type of connection


Dim X As String
Dim dealsDatabaseconnection As Connection
Dim dealsdatabases As Recordset

X = "ODBC:databse=QuantumIreland;DSN=deals;UId=sa,PWD=sa"
Set dealsDatabaseconnection = _
wrkODBC.Opentconnection("deals", , False, X)

Set dealsDatabase = _
dealsDatabaseconnection.OpenRecordset("deals", dbOpenDynamic)

Dim SQLStmt As String
SQLStmt = "SELECT deal_no, entity"
SQLStmt = SQLStmt & "FROM deals"
SQLStmt = SQLStmt & "WHERE deal_no = 16031"

dealsDatabaseconnection.Execute SQLStmt

dealsDatabase.movefirst

With ThisWorkbook.Sheets("Sheet1&quot
.Cells(i, 1).Value = dealsDatabase.Fields("deal_no&quot
End With
dealsDatabase.MoveNext

dealsDatabase.Close
dealsDatabaseconnection.Close
End Sub

 

[xlbo]

Have you thought about using MSQuery to connect to SQL Server...??
Data > Get External Data
choose Data source and ODBC type and away you go...
HTH
Geoff

 

[tyo]

thanks, but i have to use vba code to create a connection.