-
August 18th, 2009, 04:27 AM
#1
runtime error 3709
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
-
August 18th, 2009, 06:44 AM
#2
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|