ADO Connect - Access Project
Hi,
I'm using Access project as FE and SQL Server as BE. Connection from FE to SQL server is via NT login authentication and in my codes, I simply used "currentproject.baseconnectionstring" as my ADO connection string to connect to the server.
This worked fine until I realised that I might want to use SQL logins instead of NT logins to access the sql db. But with this, baseconnectionstring will not work (it will not provide the password required). To solve this, just have to make sure the user 'save the password' while logging in. But this is unsafe.
If I am not asking for too much, is there any other ways to establish a valid connection with SQL (via ADO) which having the user to save the password while logging into the Access Project file during launch?
Any advise would be grateful..
Cyherus
View Complete Forum Thread with Replies
Related Forum Messages:
Use Report Server Project To Connect To Report Model Project Data Source View?
Hello, I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view. All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View. Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string? Thanks!! -Matt
View Replies !
Upgrading Access 2003 To Access Project And SQL 2000 Backend.
Hi, I am upgrading an Access 2003 database to an Access Project with a SQL 2000 backend. I am trying to develope a type of framework to communicate with the backend. I have read some about linked tables, pass through queries ect. My initial instinct would be to use sprocs and ADO from VBA modules like a .NET application. My database has reports, forms that update and insert data, and forms the call data proccessing procedures. Is ADO from VBA modules calling sprocs the best method to manipulate my data. The DB could have up to a million or 2 records in my biggest table, their are only two end users. Any suggestions or links or books would be appreciated. Thanks, Mike
View Replies !
Connecting To SQL 2000 From Access 2003 Data Access Project
I have an Access project that is under Terminal Services (TS) on a Windows 2003 server. When I create a project I can setup a connection and see the SQL table in the 'Tables' tab in Access. I can use Design mode to view the table setup but when I try to view the data in the table I get an error saying 'Data Provider could not be initialized'. SQL server is on Windows server 2000. When I access data using a connection in VB everthing is fine, no error. I have 5 TS servers and managed to get the situation resolved on one of them about a year ago but cannot recall what was done. I seem to remember that it was something like changing one of the DLLs to a slightly older version. Does anyone know the answer to this one?
View Replies !
Cant Connect To SQL Server At Work From Home Project
HI All, i have copied a project from work i am working on and have created the same project at home in visual studio.net - everything is the same as it is at my work - for some reason though i cannot connect to the servers from my home project can anyone help me with this - i get the message "SQL Server does not exist or access denied" my web config has exactly the same set up with a key in appSettings as <add key="My_Connection" value="User ID=myID;Password=myPassword;Initial Catalog=myCatalog;Data Source=xx.xx.xx.xx" /> can anyone tell me why i would not be able to even see my work database from home? thanks in advance Gilesb
View Replies !
SQL Server Project - Failed To Connect To Databse...
Hi! I installed SQL Server on my server today and im new to it. I configured it and now im trying to make a SQL Server Project with my other computer... Then the program asks for some account information to logon to a database on the server. Then i fill it out and i get this error: Login failed for user "anaxyd". The user is not associated with a trusted SQL Server connection.
View Replies !
NEED HELP! - Unable To Connect To MSDE Database Through Web Matrix Project
I'm new to asp.net and databases and I'm currently working my way through SAMs Teach Yourself ASP.I am up to working with databases and have successfully installed MSDE using the sql2kdesksp3.exe file. However, when I try to create a new database through the web matrix project I get an 'unable to connect' error. I have tried removing and re-installing the database and I've also re-installed it using the latest file from the MS web site but I still get the same error. I've checked the obvious...that the msde service is running, password is correct, and that checks out ok.Has anyone had a similar problem and resolved it?Thanks.
View Replies !
Team Project In TFS - SSIS Project Sets Itself As The Startup Project
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. I am using Visual Studio 2005 SP1.
View Replies !
MS Access Back-end Works Fine, But SQL Server Back End NOT Working For Access Project
Hi I have 2 forms, each associated with its own table - they are linked together only by the fact that they share a common field, CustomerID.... I create a form (for table 1, called Customers) that brings up another form (for table 2, called Projects) based on the CustomerId that is selected in the first form from the Customers table - with a SQL back end it brings up ALL records in the second form (for the second table) with any CustomerIDs that exist in both tables - I set the link criteria [forms]![Customers]![CustomerID] and with an MS access back-end it works fine, but with a SQL back end it still returns ALL records with any ID that matches....not just the one I selected in the Customers form..... why?? thanks for reading this and for your help...
View Replies !
Access 2000 Project
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
View Replies !
Access 2002 Project
I post my question here. . . mostly because I don't know where else to post. I have Access 2002. I want to be able to create a database on it, but I need help. Whatever it is that I 'create', I need it to be able to 'pull' orders from my online store. I want to be able to access this database w/ all the order, payment, & shipping information already having been 'pulled' from my site. But I still want to be able to add info to it (like tracking information, customers' notes, if the item(s) are returned or exchanged). Is this possible? Where do I start?? Thanks Scarlett
View Replies !
General Access Project Info
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.
View Replies !
Access Project Connection Problems
Hi, I am running SQL server 2000 and attemptng to use client machines with access 2000 to connect to the sql database. Problems is on a client machine with the readiness updates it completely hangs intermediately when accessing the data through forms. But once the sql client utilities are installed on the client machine it works fine no crashing. Does anyone know what might need to be done to other client machines as I cannot install the sql utilities on them all! I would like access to connect to the sql server without installing the additional utils. Thanks in advance Peter UK
View Replies !
Database Size Of Access Project.
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?
View Replies !
Calling SSIS From Access .adp Project
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. Thanks for your help.
View Replies !
Access Project Connecting To SQL 2005
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.
View Replies !
Grant User Access To Project
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. Thanks, Iris
View Replies !
Import Excel To SQL Using Access Project
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?
View Replies !
Create Project In MS Access 2000
Hallo 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 Thank you
View Replies !
Can´t Open Sql Database Tables From Access Adp Project
hi there, 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 ron
View Replies !
Catching SQL Errors In An Access 2000 Project
OK, maybe using an Access project isn't the "preferred" way to deliver SQL UI solutions, but for now that's where I'm at. In converting some Access databases to SQL Server, I have a question in intercepting SQL errors (messages), especially constraint errors. You see, I'm using sprocs to populate the form, but I allow direct entry to the data using the form (that is, I'm not using a specific sproc to insert, update or delete a record - I just use an sproc to get the requested data to the form, then if the end-user adds or edits the data, the OLEDB connection between Access and SQL is handling it). The problem is that I can't (at least without doing what I just said I'm not doing - an insert sproc, update sproc, etc.) intercept the SQL constraint messages. My code will break in an OnError event, but the errors collection (both the Access errors and the ADO errors) is empty. Then SQL gives me the standard constraint (or whatever) type messages, which I don't mind, but I know my users will freak out if they see. Short of using sprocs for all my data manipulation, does anyone see a way to catch the errors before SQL Server and then to keep SQL Server from showing it's rendition of them. Since an edit can be made (not in my app, but in reality) directly to SQL Server tables from an Access 2000 project, it seems like error interception would have to happen on an application level too... If anyone can offer some insight, they would save some of the hair remaining on my head. Thanks, Dan Crisp IS Developer Surfware, Inc.
View Replies !
Security With Access 2000 Data Project And SQL 7
Hi I am trying to set up a back end database on SQL 7 and using front end Access 2000. All the tables are ready to go, I have also implemented NT only authetication method on SQL server using NT Global groups. I secured all the tables using those NT groups. When I open my Access 2000 Data Project all the users have administrative right although I restricted some of them. My question is where I can get the documentation that will explain to me how this all works? Thank you in advance.
View Replies !
Using MS Access 2003 Project With SQL Server 2005
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. Any suggestions? Thanks in advance, Mike Duke
View Replies !
Linked Tables Or Access Project, Advice Please
Hi, I am developing an SQL database with an access front end. I would like to know is it best to use linked tables or an Access project? We currently use Access 2003 but are getting Access 2007, don't know if this makes any difference. Any advice please? Thanks, Bobby
View Replies !
Access Project / SQL Server Connection Timeout Problem
Hello, 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.
View Replies !
This Is An Access Project Linking Tables On SQL 2005 Server.
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?
View Replies !
Filter Records In Access 2003 Project Form
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?
View Replies !
Access 2003 Project-SQL Express - Cant Save Desig
Hi 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 And if it is a limitation does it matter Thanks in advance Gordon W
View Replies !
Passing Parameters To Action Stored Procedures Using ADO, In Access Project
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
View Replies !
Passing Parameter Query From SQL Function To Access Project Report
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.
View Replies !
Decimal Places Error On Opening SQLServer Table From Access Project
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?
View Replies !
Visual Studio 2005 Automatically Sets SSIS Project As Startup Project
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? :) Thanks.
View Replies !
Generate Create Script To Project Option In Database Project In VS2005
When using visual studio 2005 to generate create-scripts (via popup menu item "generate create script to project" ) the script is always getting saved to Create Scripts folder under the solution. We no longer have the option of selecting our own folder. Does anyone know how to get the browse window and select the folder of our choice rather than always scriptiong to Create Scripts folder?
View Replies !
Can't Create A Setup Project For Project Referencing Microsoft.SqlServer.ManagedDTS
I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package. I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS. Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands: Dim oApp As New Application Dim oPkg As New Package oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing) Dim oResults As DTSExecResult oResults = oPkg.Execute Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard. During the creation of the setup project I get a message that states: The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project. C:windowssystem32msxml6.dll OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have. How do I find this out? Has anyone else created a project like this and experenced the same? I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.
View Replies !
PSI: How To Update Custom Field Value For A Perticular Project Using PSI(Project Server 2007)
Hi, I want to update value of a custom field for a perticular project in Project Server 2007 using PSI. I have created 5 enterprise custom fields(A,B,C,D,E) through PWA/Server Settings. I want to search all Projects on Server. If any project is having value for custom field A then I want to update rest of the custom fields(B,C,D,E) for that perticular project. I dont know how to do it please help. Thanks in Advance Madhukar
View Replies !
Unable To Use Project Professional To Establish Connection To Project Server
I fail to use project professional 2003 to access to the project server 2003 using MSDE 2000 in local area network, following message was shown, Connection failed: SQLState: '01000' SQL Server Error 1326 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()) Connection failed: SQLState '08001' SQL Server Error: 17 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. I have seen these pages with similiar cases but can't help. http://support.microsoft.com/kb/818047/ http://support.microsoft.com/kb/837653/en-us Can anyone help me?? Thanks a lot!
View Replies !
How To Store And Access Reservation Data (was &"small Project&")
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.
View Replies !
Cannot Connect To SQL From Access
I can connect to SQL from some Access forms that are in SQL test server. But when I try and connect to the same forms on a development server I cannot insert records. Even though I have changed the connection in the script. Can someone please tell me what I am doing wrong????? I do have full permissions both boxes. Thanks, Dianne
View Replies !
Problem By Design - MS-SQL Server 2000 And MS-Access 2000 Project.
Hi.Sorry for cross posting!After I've installed MS-SQL Server 2000, I've got a problem each time I opena table, view or diagram in design, in a MS-Access project.Something like a table is popping up, but only the headline of the table,there is no name in the headline. The (ghost) table is automatic gettingfocus, (like a dialog box), and I can't do anything.I can close the (ghost) table using ALT-F4 combination, but it is veryannoying me.I've tried to uninstall both the MS-SQL Server 2000 and MS-Access 2000, I'vealso tried to compact and repair the database without luck.If I connect to MS-SQL Server 7 on another computer, (same database),everything is fine.My OS is Windows XP Home edition; the MS-SQL Server 7 is running on Windows98.Does anyone here have had the same experiences?TIAJørn
View Replies !
Can't Connect With Full Access!
i have a database that i am trying 2 convert from access to SQL Server... but when i try to read the SQL Server tables through MSAccess i can't enter or edit data... the database properties, and the user permissions are NOT readonly in both access and SQL Server - so whycan't i write data? Thanks Kris
View Replies !
Prevent MS Access To Connect
Hello I'm facing a dilemma. I have quite a lot of users who need read access to data for analysis purposes. By granting them read access you also give them the option to connect to the database using MS Access which puts locks on the data. First I thought of generating stored procedures for all tables within a database those users need to query but when users execute a stored procedure with the name of the table, they get a resultset of all the data. They cannot filter the data by for example using select top 100* from usp_table where name like '%worf%' I'm sure I'm not the only one here but I don't see any alternative then grant them those rights. Thanks! Best Regards, Worf
View Replies !
Cannot Connect To Access Database
I have followed the instructions to the letter and still cannot connect to the Access database on the desktop. I am running XP Pro with Office 2003. I am using MaAfee virus scan and I disabled Access Protection, buffer Overflow protection. On-Delivery E-mail scanner and On-Access Scanner iare still enabled. . The error log on my device reads "Failure to open SQL Server with given command" and Error connecting to Access Database. Also is there a VB equivelent to the Visual studio application avaiable for download? Thanks in advance
View Replies !
|
TRACKER
