- 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 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:
Can anyone tell me which password encoded tools is used in database VB used application that must encrypt number into IUH-137610-rOI-136772-gdL-136082 Format.
Need to host an excel file (2003, 2007) on a Static server. Want to be able to allow multiple users to modify the file and have the data displayed in a static web page. Is this possible?
I am working using VBA macros in Excel (2007) to execute Stored Procedures (SP) in a SQL Server Express 2014 instance. When the SP is simply SELECTing data to return, the connection works fine.
Hi, trying to create a SSIS flow to extract data from source (AS400) to destination (SQL) db. The flow is relatively easy: connect, extract and put it in destination folder.
How can I prevent the SQL Login box when accessing SQL Azure from an Access front end? I've tried putting the credentials in the ODBC connection string for the native client and I've tried connecting from VBA;
I am really needing a solution for this if anyone can help! I am not a programer, but am proficient with Excel, Adobe, Adobe, Livecycle Designer, etc.