Most of our users are using MS Access project to work with our data in SQL Server 2005. The problem we have is that in order for them to access the tables they need to be given db_datareader and db_datawriter permission on the database. Our goal is not to give access to the tables themselves but to views. Therefore, we've created several views and placed them into a particular schema. Now we want to give access to that schema, but when we assign select, delete, insert, and update to that schema user in ms access project aren't able to view these tables. When we give them db_datareader permission they are able to see the views but in parentencies does not show the correct schema; therefore, when you try to open that view it errors out saying it doesn't exist. Also since they now have db_datareader they are also able to access the other view and tables in the database.
What we are looking to do is give our Access Project users the permission to link to SQL Server 2005 views by schema only.
My environment: Home PC Vista Ultimate SQL Server Developer 2005
I'm about to go absolutely nuts. I've tried so many things including: 1) Of course making sure Remote connections and both TCP/IP and named pipes are enabled for SQL 2005 2) Verified my connection string works. I even tested it in Enterprise Library using the tool and it connects successfully via windows authentication 3) Made sure of course my logged in user is a member of the administrator group and that my user in SQL 2005 logins has db_owner and full rights to the DB I am connecting to. I also made sure the NT AUTHORITY as well as the 3 sql service users have db_owner rights as well as the adminstrator in the SQL Server logins all have db_owner to the DB 4) Started the SQL Server Browser service, SQL Server DB engine, and SQL Server Agent 5) Made sure my site is setup right in IIS 7. I checked the connection string in there as well as it shows up in IIS7 in one of the sections. Using Windows authentication and it is enabled for my site 6) Tried running the .net site using both localhost (IIS) and also tried using the Visual Studio built-in web server. Both give this message still When I test my connection through Enterprise Library it shows test successful!
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +381 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +357 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +30 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +494 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.SessionState.SqlStateConnection..ctor(SqlPartitionInfo sqlPartitionInfo) +79
I continue to receive an error that states "The version of Microsoft Access doesn't support design changes with the version of Microsoft SQL Server your Access project is connected to. See the Microsoft update website for the latest information."
I'm using Access 2003 that I ran a database through the Upzinging Wizard with a copy of SQL Server 2005.
I have searched the Office Update pages and can't locate anything that helps, although I'm sure I'm probably missing something.
Looks like the Office 2003 MS Access has no support for creating a linked server and implement design changes. While you could link to the SQL 2005 tables, which show up as queries in MS Access 2003, any further use of the linked queries are not supported unless the Access 2003 version came out after the SQL 2005 release. What kind of an update is needed, or fix is needed so that Access projects can be developed?
I want to use SQL Server to query an Access Database with about 40,000 rows of data. If possible, I don't want to upsize the database because others need acess to it in the ACC97 format. Is there a way to use ODBC to connect to the ACC97 database so that I can use the SQL query capability of SQL server to query the database.
I know access allows you to write some SQL queries but I need the power of the SQL server and now it is a matter of curiosity because I've been searching for this answer for about 8 hours.
I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object.
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
For some reason in a Team Foundation Team Project that has multiple project types (SSRS, SSIS, WebSite, C# Business DLL...), the SSIS project makes itself the startup project to the team project. If I explicitly set another project as the startup project to the team project and then select an SSIS package in the SSIS project in the team project, the SSIS project becomes the startup project automatically.
Hi,I am using Access 2000 to access a SQL Server 2000 database. The records areread only, and I can't make any changes to them. How can I change this? I'vechecked Help, but don't seem to be able to find what I need to know.Thanks! Jill
I have recently moved from Access mdb to Access project. I have found that for the same data, an MSDE database occupies more than double the size of an Acess database! I had upsized a database with a table, comprising about 200,000 rows. Where as the Access database occupied 40 mb, the MSDE database occupied 120 mb!. Tried shrinking the database and reduced it to 113 mb. Is this to be accepted? Is there any equivalent of the very effective 'compact database' command of Access in SQL Server?
Hi all, we currently have an access databse with linked odbc connections to an SQL server database. Performance wise I don't think this is great. Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables. About 30 -40 users currently hammer the access front end during the day.
I try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?
Not sure if this is the right place to ask but I'll try...
I have to execute an integration service package in an .adp project. In other words, click of the button on the adp window has to start a package or a job.
Can that be done? And if yes, how? I've been searching the Web but without success.
How do I grant a user access to a particular project? I have set up some reports and want other users to look at them as a template to build their reports and keep getting projectname.rptproj.user is denied.
Sorry if this is the wrong forum for this, but was as close as I could get. I am an amateur stumbling thru this to "learn by doing". I am trying to set up an access front end for a SQLExpress database, currently all local for experimenting. Trying to import Excel data to a table in the database, using Access "get external data" function. I get errors and the data wont import. Error message won't give me a clue to what is wrong. All fields are named the same. If I convert the excel to a csv or tab delineated, it imports correctly. Just not the excel. Any ideas?
I am getting the overflow? message (MS SQL Server Database Wizard) on attempt of creating a project in MS Access using SQL Server 2005 Express Edition. I am actually able to open an existing DB.
I was wandering why is this happening and what can I do to create such a project
we canīt open most of the sql server tables from our Access project any more, which has to be related to collation or extended property problems.
trying to open any table will result in the information "The stored procedure has been executed but did not return any records" after the first try and later on nothing will happen any more; tables wonīt open.
basically this started to happen after recreating the sql database due to resolution 1 in Microsoft Knowledge Base Article - 318989: collation of the server is Latin1_General_CI_AS collation of the old database was SQL_Latin1_General_CP1_CI_AS, which led to the error described in the article. collation of the new database is set to <Database Standard>, now i have the problems described above.
anybody some good help on that?
ps: we canīt really rebuild the user database all the time, because we got flight data in about 4 billion records in there, so we will need anything quick and easy... thanks
How to pass parameters to MS Graph (row source is a stored procedure withparameters) placed in Access Project form ?The problem is that there is no Input Parameters property on the graphobject...
Hi, When using an Access Project 2002 connected with SQL Server Express in OLE DB it's impossible to work in creation mode. A message explains that this Access version is not compatible with this SQL version. Is there a service pack ?
I have an Access .adp file that's connected to a SQL Server. I can run small queries just fine, but some of my queries take several minutes to run. When I try to run a lengthy query I get an error message that says "Timeout Expired".
I believe that this error is the one that is described here on MSDN online:
http://support.microsoft.com/kb/243464
I tried to implement a solution similar to this in .adp file by creating this subroutine:
----------------------------------------------
Sub BypassTimeout()
CurrentProject.Connection.CommandTimeout = 0
MsgBox "The subroutine ran"
End Sub
-----------------------------------------------
I know that the subroutine is running because I'm seeing the message in the MsgBox statement. (This sub runs everytime the .adp file is opened).
However, doing this still doesn't seem to prevent me from getting the "Timeout Expired" error message when I try to run a lengthy query.
Does anyone know a way around this? I believe the default timeout is 30 seconds ... but SURELY microsoft did not intend for .adp files to not be able to run queries that are longer than 30 seconds.
I have a project need to store the information of reservation. For example, somebody wants to reserve a laptop. She will sign in the date from calender to use. I would like to let the administrator to access the database and update the database in the intranet. Also the user who want to reserve a resource can access the intranet to fill a form, after approving it, she can borrow the resource. Of course, this is small database. And we also have a report on this.Do you have any idea how to do it? I appreciate.
I have conntected an Access 2003 project to a database in SQL Express and when I try to create a new view or stired procedure I get the message
This version of Microsoft Access doesn't support design changes with the version of Microsoft SQL Server your project is connected to. See the Microsoft Office Update Web site for the latest information and downloads (on the Help menu, click Office on the Web). Your design changes will not be saved.
Is it a limitation or have I not got the appropriate permissions set up
I am trying to filter the records in an Access2003 project form using data from a control in a different form. I define the variable in form1 as RegKeyWord. The record source for form2 ends with WHERE CustomerName Like RegKeyWord. I receive the error Invalid column 'RegKeyWord' Can anyone help?
There is a form in an Access Project (.adp, Access front end with SQLServer) for entering data into a table for temporary storing. Then, byclicking a botton, several action stored procedures (update, append) shouldbe activated in order to transfer data to other tables.I tried to avoid any coding in VB, as I am not a professional, but I havefound a statement in an article, that, unlike select queries, form's InputProperty can't be used for action queries. Therefore, parameters can bepassed to action stored procedure only by using ADO through VB.As I'm not very familiar with VB, I had to search in literature.So, this is a solution based on creating Parameter object in ADO and thenappending values to Parameter collection.Please, consider the following procedure I created for passing parametersfrom form's control objects (Text boxes) to a stored procedureDTKB_MB_UPDATE:Private Sub Command73_Click()Dim cmd As ADODB.CommandSet cmd = New ADODB.Commandcmd.ActiveConnection = CurrentProject.Connectioncmd.CommandText = "DTKB_MB_UPDATE"cmd.CommandType = adCmdStoredProcDim par As ADODB.ParameterSet par = cmd.CreateParameter("@DATE", adDBTimeStamp, adParamInput)cmd.Parameters.Append parSet par = cmd.CreateParameter("@BATCH_NUMBER", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@STATUS", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@DEPARTMENT", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@PRODUCTION", adVarWChar, adParamInput, 50)cmd.Parameters.Append parSet par = cmd.CreateParameter("@SAMPLING_TYPE", adVarWChar, adParamInput,50)cmd.Parameters.Append parcmd.Parameters("@DATE") = Me.DATEcmd.Parameters("@BATCH_NUMBER") = Me.BATCH_NUMBERcmd.Parameters("@STATUS") = Me.STATUScmd.Parameters("@DEPARTMENT") = Me.DEPARTMENTcmd.Parameters("@PRODUCTION") = Me.PRODUCTIONcmd.Parameters("@SAMPLING_TYPE") = Me.SAMPLING_TYPEcmd.ExecuteSet cmd = NothingEnd SubUnfortunately, when clicking on the botton, the following error apears:"Run-time error'-2147217913 (80040e07)':Syntax error converting datetimefrom character string."Obviously, there is some problem regarding parameter @DATE. In SQL Server itis datetime, on the form's onbound text box it is short date (dd.mm.yyyy)data type. I have found in literature that in ADO it should beadDBTimeStamp.So, what is the problem ?Greetings,Zlatko
I can pass a parameter from an Access Query to an Access Report (MDB) by entering [Select Date] in the Query criteria and by placing an unbound control with a control source =[Select Date] on the report. I can't get this to work from a SQL Function Criteria to an unbound control on the Access Data Project Report. In the Function Criteria, I enter @SelectDate. In the Report control, I enter @SelectDate and it gives me an 'Invalide Column Name' error. Any idea how I can pass a parameter from a SQL Function to an ADP report?
THANKS!
p.s. I tried searching for other postings on this without any luck.
One single table when openen from a MSAccess project file gives error:
The setting for DecimalPlaces property must be from 0 through 15, 255 for Auto (default).
After which the table is openend normally. I don't recall setting Decimal Places to the table. The only numeric fields are two INTs that are also ID's (PK and FK). This error occurs also when recreating the db project. These fields can not be edited eitther from MSAccess nor Enterprise Manager.
What can be done on the table, resp. MSAccess to change this?
I have been trying to connect to access database from SQL Server 7.0. This machine is having 7.0 as a default instance and 2000 as a named instance. Also the machine doesn't have access installed and Microsoft.Jet.4.0 is of version SP8 for Windows 2000 The access database is password protected. I have tried all, OPENROWSET, OPENDATASOURCE, linked server, and ODBC.
These are some of the commands which I have tried but gives following error. ******************************************* SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'F:Geerimainilling.mdb';'bhagath';'bhagath', Employee) AS a
Server: Msg 7303, Level 16, State 2, Line 1 Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. [OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.] **********************************************
SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'DataSource="F:Geerimainilling.mdb"; User ID=bhagath;Password=bhagath;Jet OLEDB:SystemDatabase="c:WINNTsystem32System.mdw"')...Employee
Server: Msg 7303, Level 16, State 2, Line 1 Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. [OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
********************************************** SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=F:Geerimainilling.mdb;Uid=bhagath; pwd=bhagath','SELECT * FROM Employee')
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
**********************************************
I have tried all possible combinations but most of the time I come up with Error 7399 or 7303 In case if anyone has some other syntax and successful with that, please let me know
I have been tinkering with connections to SQL Server 2000 from Access 2000 for a couple of days without much luck.
My most recent attempt is the following code to the click event of a command button on a form. Most of this code was acquired from other places on the net.
I have been using Access 2000 for quite some time and am trying move the application to use SQL Server 2005 Express. Is there any way to connect directly to an Access MDB using SQL Server 2005 Express, or can it only connect to an MDF?
I am trying to stablish a shared data source to an Access database for my reporting services. Is this possible? I only see connection type Microsoft SQL Server as Connection Type.. OLEDB is not recognized. How can I achieve this?
I have an Access 97 database that I connect to a SQL Server 2000through ODBC. I have to manually set up the ODBC connection on eachuser's machine if I want them to be able to use the application though.Is there a way to store the connection string within the Access DB soI don't have to touch the ODBC settings on each user's machine? Or isthere a different solution that I'm missing? There are way too manyfor me tosetup. Thanks in advance.
I have been working on some SSIS packages for a while now and today while i was working i was trying to create a new connection and in the process there was an error and it said the BIDS has to be closed and i closed it but later when i open BIDS and try to open my project(.sln) from the file menu to work on the half done package it pops up an error which shows the path to my project location on the first line and next statement on the pop up error box says:
"Make Sure the application for the project type (.dtproj) is installed."
I tried to check some forums in which some suggested to try installing SP1 which i tried but ..i dont know why but the SP1 fails to install (i dont know if its causing problem becoz i already installed SP2 as i had some other problem before for which the cure was to install SP2).
Did anyone here face such a problem before ?
I'd really appreciate if the experts here can tell a cure for this problem.
