August 18th, 2009, 04:27 AM
runtime error 3709
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:
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
Set rs = Nothing
Set Cn = Nothing
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
August 18th, 2009, 06:44 AM
Re: runtime error 3709
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
Click Here to Expand Forum to Full Width