- 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
I am trying to develop an MVC app on my local computer and I have huge troubles opening a connection to my database. At the same time, I am able to run SQL scripts and they work just fine.
I have a web page that imports translations from a spreadsheet using Microsoft.ACE.OLEDB.12.0; The process works almost flawlessly, except that Japanese (and likely Korean and Chinese) can not be imported with this method.
Hi, Help!! How do I use a variable in a select statement in vba? This is a standard query Select IngredientID, Quantity, from Recipes where RecipeID = 7 This is what I would like to do, but do not know how to.....
Hai, I am using the following as connecting string to connect to Acess 2010 databse. This is working fine in systems with windows 2007 and excel 2007, but fails when it is windows XP with excel 2003.
web.config connection string to SQL named instance on cluster works for some time and suddenly stops working. I do the stops start of app pool and it is working again, but after some time (of inactivity) it stops working!!!
Hi, About a year ago I found this site most useful in creating a connection string in a separate workbook to a table in a complex workbook, which creates a pivot cache and is therefore read-only - Ideal for what I needed.
I want to use multiple DSN connection string in my web.config file. But its giving error as default driver not specified. Any one please help me
I have installed IIS 8.5 on my laptop with Windows 8.1. I have placed my website in c:\inetpub\wwwroot and given all the security permissions required. I am using MS Access database 2010 and configured all the features required.
Hello guys I was directed to this site as THE place to find information on how to write the connection string I need. I am still quite new at this coding stuff so any help would be appreciated.
Having trouble connecting to a MS SQL database. The OS I'm running is opensuse 13.1 64bit. I've manage to run both tsql and isql successfully. However, when I try to connect via python I get the following error message: