runtime error 3709

[Benjaminvh]

Hi all

Newby here. I am trying to establish a connection that will allow me to run VBA code through a SQL Server database.

At present I have:
Sub connect()

Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set Cn = New ADODB.Connection
Set rs = New ADODB.Recordset

Server_Name = "OM020084\BEN2005"
Database_Name = "DataCleaning"
User_ID = ""
Password = ""
SQLStr = "SELECT top 100 * FROM Other2003" (as an example)

rs.Open SQLStr, Cn, adOpenStatic

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

The code abends at rs.Open SQLStr, Cn, adOpenStatic.

Can someone please help? Feel like I have a mountain to climb.

After the connection is established, I also need a way of writing records to another table in SQL. I wrote the code for an Access database, but due to size limitations am migrating to SQL, so basically I am looking for a way to run the code through SQL.

Thanks so much
Ben

[George1111]

You have not put the connection string to the SQL database into CN

Then have not opened the CN with the right string in it

Check out www.connectionstrings.com to establish how you should formulate the string

eg, Do you have User Name / Password on the SQL database or will you be connecting using Windows Authentication