- 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 have used connection string as below in my VB macro. ConnectionString="Data Source=(DESCRIPTION = (ADDRESS=(PROTOCOL=TCP)(HOST=servername)(PORT=portno))(CONNECTDATA=(SERVICENAME=servicename))));uid=username;pwd=password;"
I have created an app in VB 2012 and cannot connect to the database. the error: 'Microsoft.ACE.OLECB.12.0' provider is not registered on the local machine. I am using Access 2013
How to connect SQL Server 2000 Database over internet? I have written the code as follows: db.Provider = "SQLOLEDB; Data Source=xxx.203.xxx.135,1433; Network Library=DBMSSOCN; Initial Catalog=accinv; User Id=sa;"
Target: Microsoft SQL Server Express Edition with Advanced Services (64-bit) OS: Microsoft Windows NT 6.1 (7601) Version: 10.50.1600.1 I have tried close to 30 various of the following, none of which work, any insight/suggestions would be great appreciated!
Hi,I have developed an application using Visual Studio 2008 on C# for stand-alone device, working under Windows Embedded Compact 7. Application work with SQLCompact 3.5 . using System; using System.Linq;
I have an ASP.NET C# application with a SQL server 2012 database that has consolidated membership provider and application database. I am trying to modify my web config according to the instructions provided by the hostforlife.eu hosting service but I think I'm missing something.
I am trying to connect to sql server 2008 r2 from Excel 2010 (VBA). the process is for internal purposes only. I need to pass an id and password to connect to the db from excel.
I have executed Teradata Queries using VBA within Microsoft ACCESS for years. Now using the same connection string with ACCESS 2013, I receive the Error Message: Run-time error '-2147467259(80004005):
Does anyone know of the OLEDB drivers will be supported in SQL Server 2014? I read in some post it would not be available after SQL 2012 but in the OLEDB documents on the Microsoft site it shows a topic under SQL 2014. Thanks.
Hi. I have an database in SQL Server 2012 installed in my server machine. When an aplication developed in VB2013 perform access in database, the same aplication in other station don't can access the database.