- Q & A
- ask question
- log In
- Copyright 2014
- © ConnectionStrings.com
- All Rights Reserved
- Powered by CSAS
- With support from Contributing Developers and Windward Reports
Here's the setup:
MS Access has a linked table to SQL Server. SQL Server has a password on it. Trying to use Excel VBA to query this linked table in Access, but I can't get the connection string correct. I can connect from Excel to Access, or from Excel to SQL Server, but I can't figure out how to go Excel --> MS Access --> SQL Server. Need help.
Thanks for your time.
Cory0 asked Cory No
Please share the connection strings you are using. There are two separate right? One for Excel -> Access and one for Access -> SQL Server.
Is there any error? Please update your with error messages. Or specify more precisely the problem / what's happening when connecting? Right now it's a bit to vague I think...0 Max Wikström
Ooop. Sorry about that. I only have one connection string (from Excel to Access, with Access already having a linked table to SQL Server established). I'm not sure how to add a second connection string. Basically, when I go to open the linked table in Access, a prompt window pops up that asks for the Login ID and Password for the SQL Server. Here's the code I'm using so far:
Function DatabaseCall2(ByVal sSQL1 As String) As ADODB.Recordset Dim Cn As ADODB.Connection Dim RS as ADODB.RecordSet Dim ConnectionString As String Set Cn = New ADODB.Connection 'DSN=FIDM_Reporting 'DATABASE=fi_analytics_reporting ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=FIDM_Reporting;" & _ "User ID=UserNameHere;Password=Passwordhere" Cn.ConnectionString = ConnectionString Cn.Properties("Prompt") = adPromptAlways Cn.Open // Will add code here to execute SQL statement and assign RS, but I haven't gotten past the previous step End function
When I run this code, I get the prompt that asks for Data Source, User Name, Password, Provider String, and Open Mode. I've been Googling this for a few hours, and I'm not sure what to put in for Data Source. Is it the SQL Server DSN? What about Provider String?0 Cory No
Remove (or comment out) the line
Cn.Properties("Prompt") = adPromptAlways
to get rid of the prompt. (cause its the prompting thats your problem right?)0 Max Wikström
Hi All, I am trying a Tricky one here, Request your help on the same. I have access 2007 Data base, Present in Share point. I have a Excel macro which Interacts with Access Database.
My backup database of sql server 2012 has been damaged. How can i restore backup file in sql server 2012?
Hello, I am a newbe to programming. Is there a way to connect to multiple databases using ADO. I'm currently using the following string to connect to one database. dbname1 = "FireReportingTest"
I've got some long running data profiling queries that I am running against a SQL Server 2008 DB. Here is the connection string that I am using with the .NET System.Data.SQLClient provider.
Hi, I have created my Sql database in my local server using sql authentication..Created windows application using vb.net.When i install my setup file in my networks computers it runs successfully..but
hai I am getting an error called 'UserInfo' is not a member of 'User'. in the following code Select <% Dim i As Integer For i = 4 To 7
how to recover from the error "Name '__o' is not declared" I was trying to run the project which was developed in .netframework2.0 on .netframework4.0, but frequently i am getting this error like above 30 times.
Connection string to SQL Server 2012 "LocalDB" April 2, 2014 Hello, Could I get a little help on my very first attempt to write an app with code that connects to my own database table on my own computer. Using Windows8.1, Visual Studio 2013 Express bundled with SQL Server2012 Express localDB.
Now I'm using oledb in order to database connection on the c/c++ application program. Provider=SQLOLEDB;Data Source=127.0.0.1,1433; FailoverPartner=Partner_datbase_server_name;Database=databasename;User Id=id;Password=password;Network=DBMSSOCN;
I'm trying to create a simple app that has a search field that looks for records in a database. Once a record is found, it can be clicked on which populates a template with info to print a certificate.