![]() ![]() Given SQL Authentication (with a hard-coded user name and password) for SomeUser with SomePassword: connString = "Provider=SQLOLEDB.1 Data Source=SomeSqlServer Initial Catalog=SomeDatabase UID=SomeUser PWD=SomePassword " Given a SQL Server instance named SomeSqlServer, a database named SomeDatabase, and using Windows Authentication, the ADODB connection string would look like this: Dim connString As StringĬonnString = "Provider=SQLOLEDB.1 Data Source=SomeSqlServer Initial Catalog=SomeDatabase Integrated Security=SSPI Persist Security Info=True " GetWorkstationId = Environ$("ComputerName") You can get the computer name by fetching the environment variable value, using Environ$: Private Function GetWorkstationId() As String Since you're using ADODB, you'll want to specify a Provider, a Data Source and, optionally, an Initial Catalog: Provider=SQLOLEDB.1 Data Source=SQL Server instance name Initial Catalog=Database name Įach connection can be monitored on the server when building your connection string you can optionally specify a Workstation ID to identify the machine the connection is for. Integrated Security=SSPI Persist Security Info=True Ĭonnection strings are annoying - seems there's a different format/wording for every single different thing that's able to parse them! That way you are keeping passwords and usernames out of hard-coded strings, and let the network deal with authentication.Īssuming you don't want to be maintaining passwords in dozens of copies of macro-enabled workbooks across your network, you'll want to use Windows Authentication. If you're connecting to SQL Server, you should prefer Windows Authentication if that's available: you create a Login at server level for a group of Active Directory users, and then you create a Windows-Authenticated User in your database using that login. Set rs = cnn.Execute("Select top 100 * from "TABLE NAME" aac " & _Ĭan someone walk me through the connection string and how to set it up step by step? Thank you. ' Create a Recordset by executing an SQL statement. ![]() "server=SERVER NAME uid=USER ID pwd=MyPassword database=DATABASE NAME" ' Open a connection by referencing the ODBC driver.Ĭnn.ConnectionString = "driver= " & _ This is the code I am using and which is giving me an error. ![]() I know the database name, server name and my user name. Also, I would need the user authentication by Windows authentication. I think I am setting up the string connection wrong. I have read every similar question here on stack overflow but when I try to replicate them, I always get an error, ussualy that the login failed for user. I would like to set up a vba code that would connect to sql management studio 2012 a run the query, which I would specify in the vba code. ![]()
0 Comments
Leave a Reply. |