Creating an ADODB connection

[deharris2003]

I am trying to run a query in Access that will update an SQL table, Which I know I can do. Problem is the query always asking for the DSN. It is my inderstanding that if I run the Query in VBA then I can code the ADODB connection string. I know how to do this in ASP but VBA is different. Here is what have already and it does not work. Any help is much appreciated.

Set Conn = Server.CreateObject("ADODB.Connection&quot
Conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=data1;UID=DBS_TEMP;PWD=DBS_TEMP"

strSQL = "DELETE FROM dbnline_Media " & _
"WHERE dbnline_Media.IDX_HP_ID = '184'"

Conn.Execute (strSQL)

 

[cmmrfrds]

Dim cn As New Connection
Dim ref as integer, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

cn.execute strSQL, ref

if ref > 0 then
Debug.Print "Number of records deleted = "; ref
end if
cn.Close
Set cn = Nothing

Make sure you have a reference set to the ADO library.