- 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 Every one, I need to connect DBvisualizer with AS400 in local machine. Can any one help on this with step by step procedure. Because i am new to this type of connections.
Jet 4.0 is now in a deprecated state so we intend to redevelop our applications (which currently access Access via DAO) to use something more "future proof".
Hi! I'm trying to create a test DB in SQL Server 2008, using Visual Basic 2010 Express. I'm failing each time--no message, just nothing shows up. Is it my connection string? Private Sub createDB()
Dear All, how to write the connection string of a file which is XML spread sheet 2003 my file named test.xml and its contains is: <?xml version="1.0"?> <?mso-application progid="Excel.Sheet"?>
Dear all, how to write the connection string of XML File which represent a Spread Sheet document
Hi all , I have two servers in the same domain name , I installed a WebService on IIS one server,, the webservice read data from sql server 2008 database stored on another server ,
Could you help me? I made a small webservice in c#, there is an oledb connection. The webservice is operate from developer tools perfectly, but in the IIS only appear, and the connection does not work.
How to write connection string to 2003 Excel file( .xls ) with Operating system 64 bits because the provider " Provider=Microsoft.Jet.OLEDB.4.0 " not exist in 64 bits OS.
hi Is it possible, I recover my forgotten Excel file password? few days ago i have lost my excel password and now i unable to open my password protected Excel file can anyone help me how to open password protected Excel file ??????
i encounter the following errors: 'connection' is ambiguous in the namespace 'ADODB' and 'Recordset' is ambiguous in the namespace 'ADODB'