How To Connect To A SQL Database From Access 2002
Currently i have a SQL database on our server and an adp file that allows the end user to open the database to play with the data.
On the client side the user navigates to the adp file from above and opens it which allows them to do their work.
I need the same database but for another department, so i copied the SQL and adp file to another folder and renamed it. I then open the adp file from the new folder created using Access 2002 on Win XP Pro SP2, click File navigate to connection and refresh the data source and server name but i cant see the new database i copied over.
I then stop/start the Sql server but same issue. I now think i might need an ODBC connection but when going into ODBC the other database has no connection setup in there therefore i guess i dont need to for the new database. I type the name of the new database in connection (Access 2002) and comes back with an error (Cant find it)
Any idea about this one? all im trying to do is have the same database with a different name and allowing clients to connect to it?
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
Errors Upsizing An Access 2002 Database To SQL Server 2005
I have an Access DB with about 50 tables. When I run the upsize wizard all bur four tables import beautifully, data and all. Four of the tables fail with the message 'Table was skipped, or export failed' When I try to IMPORT the data I get 100 or so messages like this one: Warning 0x80047076: Data Flow Task: The output column "AssessPlanPHPID" (23) on output "OLE DB Source Output" (11) and component "Source - tblAssessPlan" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance. (SQL Server Import and Export Wizard) And the import fails. Note that the upsize creates the tables just fine, it just won't import the data. Any ideas? I apologize for the fonts jumping around.
View Replies !
Moving Access 2002 Db To MS SQL
Hey guys, I was just trying to get some information on how to move an Access 2002 db to MS SQL. Also, what all do I need to be able to access the db from the web. For instance, query the db for info from a website. I don't have that much experience in SQL but I quiet a few programming languages so i'm sure i'll learn it quick, I usually do. Right now I have a server set up with MS SQL server 2005 with all the other things required for testing over in my own little world. DNS, Active Dir., etc.... I'm trying to do this because a friend of a friend has a business(small businessish) who wants to be able to view reports from queries over the internet. And i'm sure I can do it but i told them to let me give it a test run to make sure I can before we commit. Thanks in advance.
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 !
HELP!!! How To Upsize From Access 2002 To SQL Express
Problem:Upsize a backend MSAccess 2002 Database to SQL Express 2005Explored:Tried using the upsizing wizard from Office XP(2002), Two tables always get skipped.***! The two tables skipped data only, the tablename and data structure were created.Tried to install UPSize Pro, installation failed.I decide to try it in VWD 2005, here is my code so far but it keeps erroring out. Dim cn As System.Data.OleDb.OleDbConnectionDim cmd As System.Data.OleDb.OleDbDataAdapterDim ds As New System.Data.DataSet()cn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:Documents and SettingsBillMy DocumentsVisual Studio 2005WebSitesWebSite1App_DataData for Database.mdb';Persist Security Info=True")cmd = New System.Data.OleDb.OleDbDataAdapter("select * from Service_Orders", cn)cn.Open()cmd.Fill(ds)cn.Close() Dim connDest As New Data.SqlClient.SqlConnection("Data Source=WLOCKLAPTOPSQLEXPRESS;Initial Catalog='Data for DatabaseSQLND1';Integrated Security=True")connDest.Open()Dim oBCP As New Data.SqlClient.SqlBulkCopy(connDest)oBCP.DestinationTableName = "Service_Orders"oBCP.WriteToServer(ds)oBCP.Close()connDest.Close() It erors on Line oBCP.WriteToServer(ds)with......System.InvalidCastException was unhandled by user code Message="Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.IDataReader'." Source="App_Web_hb6xyamq" StackTrace: at ASP.xfer_data_aspx.Button1_Click(Object sender, EventArgs e) in C:Documents and SettingsBillMy DocumentsVisual Studio 2005WebSitesWebSite1xfer data.aspx:line 30 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)I'm open for ANY suggestions, I do not have access to DTS, its not in the Express addition. Thanks in advance.Bill
View Replies !
Convert MS Access 2002 To SQL Express
I would like to upsize an MS Access 2002 database I have to an MS SQL Express database MDF file. In other words I want to keep a file database (not go full blown SQL Server) so I can copy and past the database to deploy it as I can do with Access. How can this be done?? Thanks George
View Replies !
Importing Data From Access 2002 In SQL Express
Hi All, At the moment i have a piece of software that uses Access to store the data into a number of tables. I am developing a new piece of software that has been built around SQL express and need to upgrade users that are using access. I have managed to write the installation to install etc and also the start of a script to insert all of the tables. I cant work out how to get the data into SQL express using a script. Basically the access db (CentralDb) will be stored a a given location C:Program FilesCentralDB.mdb The tables within the SQL version are indentical to access but i need a way of pulling out the information stored and putting it into SQL, and as there is no DTS i am becoming stuck on how to automate the process. Any ideas? Thanks in advance Martin
View Replies !
Unable To Update Table Using Access 2002 Front End
I am unable to update a table (either by opening it and entering datadirectly or through a form). I have set the recordsettype property toupdateable snapshot and have set the permissions to update inside ofthe SQL Server 2000 to checked. Unfortunately, these items have notworked. Any advice is appreciated. Thanks.Dave Christman
View Replies !
Access 2002 To MSSQL Upgrade Challenge With ASP Web Application
To whom it may concern, I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red. Thanking you in advance Darren <!-- #include file="./../../common/includes/i_header_login.asp" --> <link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css"> <%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames) Set objCSVFile = CreateObject("ADODB.Stream") Call objCSVFile.Open If IncludeFieldNames Then 'string concatenation issues aren't a problem for a small string of field names For Each Field In RS.Fields If FieldNames = "" Then FieldNames = Field.Name Else FieldNames = FieldNames & "," & Field.Name End If Next FieldNames = FieldNames & vbCRLF Call objCSVFile.WriteText(FieldNames, 1) End If Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, "")) Call objCSVFile.SaveToFile(CSVFilePath, 2) Set objCSVFile = Nothing End Sub %> <% function evaluate(pFormula, pUserID, pMonth, pYear) if isnull(pFormula) then evaluate = 0 exit function end if x = 0 key = "" zero = "0" nine = "9" eFormula = "" lf = len(pFormula) for i = 1 to lf c = mid(pFormula,i,1) if x = 1 and c >= zero and c <= nine then key = key & c if x = 1 and (c < zero or c > nine or i = lf) then set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key) eFormula = eFormula & rsd.fields(0) rsd.close key = "" if c < zero or c > nine then x = 0 end if if c = "v" then key = "" x = 1 end if if x = 0 then eFormula = eFormula & c next on error resume next evaluate = round(eval(eFormula),0) 'if err then evaluate = err.description & ": " & eformula if err then evaluate = 0 end function %> <% period = split(request.form("period"),",") if ubound(period) = 1 then mmmm = period(0)+0 yyyy = period(1)+0 else mmmm = 0 yyyy = 0 end if %> <div align="left" class="MainBodyText" style="width: 100%; height: 100%"> <div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" > <% if session("groupid") = 1 then%> <a href="../main/administration.asp"> <<< Administration Home</a><br><br> <a href="manager_report.asp"> <<< Managers Report Home</a> <%end if %> <%if session("groupid") = 2 then%> <a href="../main/manager.asp"> <<< Manager Home</a><br><br> <a href="manager_report.asp"> <<< Managers Report Home</a> <%end if %> <%if session("groupid") = 3 then%> <a href="../main/user.asp"> <<< User Home</a> <%end if %> <%if session("groupid") = 4 then%> <a href="../main/general.asp"> <<< Home</a><br><br> <a href="manager_report.asp"> <<< Managers Report Home</a> <%end if %> </div> <p align="center" class="MainBodyText"> <img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p> <p align="center" class="MainBodyText"> To use the Print Page function please select the period required then select view report. You can then select Print Page which will print out the rendered page.</p> <form name="f1" method="POST" action="dialmtd.asp"> <div id="printReady"> <table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2"> <tr> <td align="left" width="127"><font face="Arial" size="2"> <select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737"> <option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report <% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID") do until rs.eof if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="143"><font face="Arial" size="2"> <select name="period"> <option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period <% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2") do until rs.eof x = rs.fields(1) & "," & rs.fields(0) if request.form("period") = x then selected = "selected" else selected = "" %> <option value=<%=x & " " & selected%>><%=rs.fields(2)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="117"><font face="Arial" size="2"> <select name="stateid"> <option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States <% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state") do until rs.eof if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="127"><font face="Arial" size="2"> <select name="countryid"> <option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries <% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country") do until rs.eof if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td> <td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td> </tr> </table> <% if mmmm > 0 then %> <table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4"> <thead> <tr> <% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup") if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid") if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid") set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name") lastfield = rs.fields.count-1 s = 4 %> <td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td> <td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td> <% for i = s to lastfield %> <% next %> </tr> </thead> <tbody> <br> <% dim v() do until rs.eof min = int(rs.fields("minimum")) mintype = rs.fields("mintype") sumv = 0 avg = 0 n = 0 for j = s to lastfield redim preserve v(j) v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy) sumv = sumv + v(j) n = n + 1 next if n > 0 then avg = round(sumv / n) %> <tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr> <tr> <td bgcolor="#FFFFFF"> <p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td> <td bgcolor="#FFFFFF"> <p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif"> <% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif"> <% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif"> <% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif"> <% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif"> <% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif"> <% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif"> <% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif"> <% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif"> <% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif"> <% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif"> <% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif"> <% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif"> <% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif"> <% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif"> <% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif"> <% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif"> <%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td> </tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield color = "#000000" if int(v(j)) < min then color="#FF0000" %> <% next %> </tr> <% rs.movenext loop rs.close cn.close set cn = nothing %> </tbody> </table> <% end if %> </div> </form> </div> <!-- #include file="./../../common/includes/i_footer.asp" -->
View Replies !
SQL 2k Backend Access 2002 Frontend - Random 'object Invalid Or No Longer Set'
Hi there, we have a SQL 2000 SP3 backend and MS Access 2002 SP3 frontend. We have replication running across 2 sites where we have used diffeerent seed code for each site. A 'Merge' of data changes occurs every 5 minutes. We have approximately 30 users, 15 at each site. 1 site is 24 hours operation the other is 8.30 - 5.00 Our problem is that during office hours 8.30 - 5.00 we get the occasional 'object invalid or no longer set' message appear on the Access frontend on a random user at the 24 hour site. It does not occur after the 8.30 - 5.00 site goes home. It does not follow the amount of data changes, sometimes it will occur after 18 data changes have merged, sometimes there can be hundreds of data changes merged. The 'object invalid or no longer set' message appears after the front-end has refreshed. You can click O.K. button to make the message go away but there are usually more behind it. Once they are gone if you touch any of the buttons in the frontend they appear again. Access must be closed and re-opened to continue working. All clients are Windows XP SP2 at both sites. So latest version of Jet is in use. I have good network connectivity at both sites. Even our SQL/Access support consultants are baffled. Any educated suggestions would be very appreciated.
View Replies !
How Do I Set Access 2002 Upsizing Wizard To Create Clustered-index On SQL 2005
Here is an extract from the Acc2Sql2.doc >> By default, the Upsizing Wizard transfers all indexes as nonclustered indexes. You can modify the Upsizing Wizard to transfer the primary key index to a clustered index. To make this change, start Microsoft Access and open the upsizing wizard library database. For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the filename is Wzcs97.mda. When the database is open, click the Modules tab and open the UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change the default value from False to True. <<< end quote Question: I am using ACCESS 2002 upsize wizard, I searched my computer for *.mda and could not find any wzcs*.mda. How do I set it so that it creates clustered-index instead of non-clustered-index?
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 !
Ssis, How To Connect To Access Database?
MS Access database was one of the standard data connections in DTS. Excel is listed as a scourc/destination, but access is not. Question: What is the best connection to use for MS-Access databases for output of data? We generally process the ETL in SQL server then, export to Access database. THANKS!! Dave
View Replies !
Mysterious Mystery -- 2002-11-08/2002-11-09
I have function that returns a table of information about properties. Thedata comes from three different tables -- addresses (called PropertyID),property characteristics, and events concerning those properties (sales,appraisals, etc.), plus a table that maps one representation of propertytypes into another. The records are selected on the basis of location(longitude & latitude), property type, event type, and a range ofevent dates (upper and lower date specified). There are tens of millionsof records of all types, and almost any location, property type, event typeand date range will yield records.The heart of it is a cursor that selects records from joins on this basis:SELECT <a bunch of fields>FROM Property dJOIN PropTypeMap ptm ON ptm.PropertyTypeID = d.PropertyTypeIDJOIN PropertyID a ON a.PropID = d.PropIDJOIN Event e1 ON e1.PropID = d.PropIDLEFT OUTER JOIN Event e2 ON e2.PropID = d.PropIDWHEREd.LastSaleDate >= @LoDateAND a.GeoLongitude BETWEEN @LowerLon AND @UpperLonAND a.GeoLatitude BETWEEN @LowerLat AND @UpperLatAND ptm.PropCategory = @PropTypeAND a.GeoMatch <= @MinGeoQualityAND e1.EventTypeID = @SaleEventTypeAND e1.TransactionType = 'R'AND e1.EventDt BETWEEN @LoDate AND @HiDateAND e1.EventAmt > 0AND e2.EventTypeID = @AssessmentEventTypeAND e2.EventDt <= @HiDateAND e2.EventAmt > 0Each property has one PropertyID record, one Property record, and N Eventrecords (average perhaps five).What is the mystery? If @HiDate, which is the upper end of the time window,is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,oodles of records are found. I get the same query plan for either one, andbased on the content of the data, they should return almost exactly the sameset of records -- exactly the same set in almost all cases, in fact.Is 2002-11-08/09 some sort of magic dividing point? I have replicated thison the large database and on a smaller test version on another SQL Server.(SQL Server 2000) I dropped the indexes and tried it, andthe same thing happened. This is driving me crazy!
View Replies !
DTS Connect To Access 97 Secure Database Trouble...
Ok, I need to give some details. I have a secure Access Database using WorkGroup Security logon. It is an Access 97 database (yes I know it is "old"). I tried to create a DTS package in SQL Server 2000 to connect to it, but I am getting the same error that I got before I "joined" the workgroup when attempting to access the DB. So I changed the Service logon identites to mimic <me> as the logon, and still I can't get it to connect. The user name and password I put in the connection are correct. It seems like nothing is working and I am all out of ideas. Does anyone have any recommendations? Has anyone done anything like this successfully? PS: I can manually logon to the database, and extract the data 1 table at a time, but I don't want to export 40 tables each time I have to load this data. Any help would be appreciated. Thanks in advance. Frank
View Replies !
What Syntax Should I Use To Connect To Access DB (having System Database) Via OPENROWSET?
MS SQL Server 2005 Express. I'm trying to connect to Access DB (having System Database) via OPENROWSET. Everything (client, server and access file) is on local drive. This works (ODBC): select * from openrowset('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:MBK.mdb;SystemDB=C:SECURED.MDW;Uid=me;Pwd=pw;', 'select * from [Mbk]') This works (Jet.OLEDB): select * from opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:MBK.mdb;Jet OLEDBystem Database=C:SECURED.MDW;User ID=me;Password=pw;') ...Mbk This won't work (Jet.OLEDB): select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'MS Access;Database=C:MBK.mdb;System Database=C:SECURED.MDW;UID=me;PWD=pw;', 'select * from [Mbk]') saying ... "Wrong argument". This won't work (Jet.OLEDB): select * from openrowset('Microsoft.Jet.OLEDB.4.0', 'MS Access;Database=C:MBK.mdb;SystemDB=C:SECURED.MDW;UID=me;PWD=pw;', 'select * from [Mbk]') saying ... "There are no permissions for usage of object C:MBK.mdb". It seems that it simply hasn't found system database file C:SECURED.MDW, cause when I change SystemDB=C:SECURED.MDW to something like BlahBlahBlah=C:SECURED.MDW the same message is shown. So, what is the right syntax for stating System Database in OPENROWSET query string? And why 'System Database' won't work? Thank you.
View Replies !
Unable To Connect To SQL Server Database CANNOT ACCESS THE OLD THREAD
The reason I had to start a new thread was that the old thread seems to be closed. There is no "Reply" button showing. I still have the same problem. The last post asked if I was sure I was using SQLExpress. I only have SQLEXPress and I have never had anything else. Therefore the question becomes - Why does the system seem to think I have another version? Alan
View Replies !
SQL Server 2005 Database Connect To MS Access 2007 With Error
Dear all, I am running an Access adp application with SQL Server 2005 as back end database. I run a query by using Management Studio query window, and it returned correct results with some columns containing NULL value. But when I run this query through MS Access client side, popup an error "Data provider or other service returned an E_FAIL status" and crash the Access application. I moved the database back to SQL Server 2000, and it runs perfect on both client side and server side returning the correct result. This query is important for the application. Please help!!!! Query as followed: SELECT TOP (100) PERCENT dbo.VWINFO312FYTRStreamEnrolments.StudentID, dbo.RequiredStreams.StreamType, dbo.VWINFO312FYTRStreams.StreamCode + CAST(dbo.VWINFO312FYTRStreams.StreamNo AS varchar) AS FullStreamCode, dbo.DaysOfWeek.DayCode, dbo.VWINFO312FYTRClasses.StartTime, dbo.VWINFO312FYTRClasses.EndTime, dbo.VWINFO312FYTRClasses.Room, dbo.Tutors.TutorName, dbo.Tutors.PhoneExtn, dbo.Tutors.OfficeHours, dbo.DaysOfWeek.DaySequence, dbo.RequiredStreams.StreamOrder FROM dbo.RequiredStreams INNER JOIN dbo.VWINFO312FYTRStreams ON dbo.RequiredStreams.PaperID = dbo.VWINFO312FYTRStreams.PaperID AND dbo.RequiredStreams.StreamCode = dbo.VWINFO312FYTRStreams.StreamCode INNER JOIN dbo.VWINFO312FYTRStreamEnrolments ON dbo.VWINFO312FYTRStreams.PaperID = dbo.VWINFO312FYTRStreamEnrolments.PaperID AND dbo.VWINFO312FYTRStreams.StreamCode = dbo.VWINFO312FYTRStreamEnrolments.StreamCode AND dbo.VWINFO312FYTRStreams.StreamNo = dbo.VWINFO312FYTRStreamEnrolments.StreamNo LEFT OUTER JOIN dbo.DaysOfWeek INNER JOIN dbo.VWINFO312FYTRClasses ON dbo.DaysOfWeek.DayCode = dbo.VWINFO312FYTRClasses.DayofWeek ON dbo.VWINFO312FYTRStreams.PaperID = dbo.VWINFO312FYTRClasses.PaperID AND dbo.VWINFO312FYTRStreams.StreamCode = dbo.VWINFO312FYTRClasses.StreamCode AND dbo.VWINFO312FYTRStreams.StreamNo = dbo.VWINFO312FYTRClasses.StreamNo LEFT OUTER JOIN dbo.Tutors ON dbo.VWINFO312FYTRClasses.ResponsibleTutor = dbo.Tutors.TutorID
View Replies !
Looking For A SQL Database Administrator With Working Knowledge Of SQL Server 2002
This is a permanent/full-time position based at Simi Valley, CA, 93062 Please see the enclosed job description: Primary Responsibilities Include: This position demands a Microsoft SQL Server Database Administrator in a Production DBA role with a working knowledge of Microsoft Clustering and operating systems. The candidate should be proficient in trouble shooting, performance tuning, replication and backup/recovery strategies. The individual should possess excellent communication, facilitation and negotiation skills. The individual must be comfortable adapting to a rapidly moving client focus and must be adept at multitasking. Knowledge and expertise in SQL high availability and EMC SAN environment is a plus. The individual has to support a distributed application for users in multiple locations and therefore should possess strong problem solving, debugging and database maintenance skills. Also critical would be the ability to manage change and effectively test fixes and enhancements and adhere to SDLC principles. The individual will provide SQL Server database administration (DBA) support and technical support for multiple applications in development, integration test, user acceptance test, quality assurance, performance and production environments. The position will consist of both systems and application DBA administration responsibilities for large-scale SQL Server databases on multiple clustered and non-clustered Windows platforms. Database duties include: Database creation and configuration, performance tuning, code migration, disk space management, database backups, database performance and resource monitoring, and trouble shooting. Other duties include creation of backup plans, test disaster recovery scenarios, manage security and spec out hardware requests from business units. The individual must have senior level experience managing Microsoft SQL Server 2000 databases and experience with Analysis Services, OLAP and Data Warehouse. Working knowledge of SQL Server 2005 The individual must have strong communication skills, both verbal and written. Additionally, the individual must be able to work in teams consisting not only of their peers, but representatives from our applications development and operations teams, who in effect are our customers. The DBA will have overall responsibility to ensure work is performed, including tasks assigned to multiple DBAs. The DBA will be the primary attendee at project meetings, and must work well with application developers and managers, especially when differing opinions occur over technical approaches. The DBA is expected to be an effective influencer, having good collaborative skills, and be effective at implementing our standards, policies, and procedures. The DBA also must have a broad knowledge of the technical aspects of SQL Server databases where client applications use .NET Framework with Visual Basic, Visual C++ and Visual C# based code and middle-ware such as WebSphere and IIS. Besides strong skills in Database Administration, preference will be given to individuals with working knowledge and experience in EMC SAN technologies, HP/Compaq Hardware, NetIQ AppManager, Remedy, Symantec/Veritas NetBackup and Microsoft Operations Manager. The individual will be assigned on-call responsibilities on a rotational basis, which often requires work after normal business hours. A Successful Candidate Will Have: Requirements: Bachelors€™ degree in Business or Computer Science or equivalent work experience. Microsoft Certification as MCDBA is a plus. Minimum 5+ years experience in SQL Server Database Management. Strong Windows OS skills. Strong technical understanding of Microsoft SQL Server 2000 internals. Technical understanding of prior SQL Server versions including 6.5 and 7.0. Broad knowledge in various backup and recovery methodology's (NetBackup a plus) Strong skills / understanding of database performance factor, monitoring tools and recovery procedures. Strong analytical skills in fault detection and resolution. Strong skills in DBMS maintenance tools. Team work orientation a must. Strong people / customer skills a must. The Ideal Candidate May Also Possess: Post-graduate degree If you are interested, please send your Updated Resume in Word Format and the following details as soon as possible. 1. How quickly can you start? 2. What is your expected salary? 3. Is location OK? Do you need to relocate? 4. If hired, will you be able to provide documentation of your eligibility to work in the US for all employers? 5. If requested, would you be able to provide 2-3 professional references? 6. Best time to call you and your contact numbers. If you have any questions do not hesitate to contact us. We look forward to hearing from you soon. Karthy Nair Technical Recruiter Hartwell Global €“ People Are Our Business! Karthy@HartwellGlobal.com Ph: 818-338-3240 Ext: 319 31194 La Baya Dr Ste 202 Westlake Village, CA 91362 http://www.hartwellglobal.com
View Replies !
Error Message: &&"Cannot Open Database.[DBNETLIB][ConnectionOpen(Connect()).] SQL Server Does Not Exist Or Access Denied&&".
I installed SQL 2005 including backward compatibility, MSDN libraries and SP2 a new Windows 2003 server (chose mixed mode authentication). Installation was successful and I then installed an off the shelf database with Windows authentication which also installed successfully. I created a new group in AD, added the database users into the group and gave db-owner rights to the group in SQL as advised by the installation guide. However, when I try to open the database it gives an error message saying "Cannot open database.[DBNETLIB][ConnectionOpen(Connect()).] SQL server does not exist or access denied". I also tested it adding an individual user (myself) as a user to no avail. When I set up DSN in ODBC on my computer I can successfully test the connection but can't run the application. The connection is via TCP/IP. Any suggestions?
View Replies !
Connect To RDB Database-CONNECT TO ORACLE RDB HYC00 (Driver Not Capable)
i am using reporting services to prepare reports. My database though is not SQL is RDB. I using ODBC to connect to RDB with "Oracle ODBC for RDB" Driver version 2.10.17.00. This is my data source. When i use this datasource to create a report when designing a query i get an error "CONNECT TO ORACLE RDB HYC00 (Driver not capable)" and i cannot see the tables graphically set the relations and prepare the query. The only way is to write manually the statement in rdb format and this is very difficult. Is there a way to be able to design my query graphically?
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 !
Database Access Via COM Objects - V- Database Access Via Stored Procedures
We have been asked to look into using stored procedures with SQL Server 7.0 as a way to speed up a clients site. 99% of all the articles I have read along with all the books all say Stored Procedure should be used whenever possible as opposed to putting the SQL in your ASP script. However one of my colleagues has been speaking to Microsoft and they said that that they were surprised that our client wanted to use Stored Procedures as this was the old method of database access and that now he should really consider using COM objects for data access as itis much faster. Has anyone got any views on this or know of any good aticles regarding this matter ?
View Replies !
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 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 !
Connect Remotely To Sql Express Using MS Access
I want another PC (not local) to connect to SQL Express database using MS Access, I cannot find anything on how to start, is there a tutorial or guide somewhere. The location is in another city and I want someone just to view the tables on their PC using Access. I have set everything up my end with ports and TCP connections etc.. Many thanks Regards Richard
View Replies !
Best Way To Connect MS Access To SQL 2000 Tables?
Hello All; I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end? Can anyone answer that simple question? Grarful for ANY resonse, Larry.
View Replies !
Do I Need ADO 2.8 To Connect To SQL Server 2005 From Access?
We have just upgraded to SQL Server 2005 from 2000. We have an MS Access application that connects and links tables from the new SQL Server database. Users are getting errors when creating records for the first time on the subscriber databases. We have reseeded the primary keys on all the tables to no avail. The only thing we can think of that may be a problem is the version of ADO that we're using as a reference in the MS Access application. We have a reference to ADO 2.5 and don't know whether we should be using 2.8. Any suggestions? Thank you.
View Replies !
Connection Manager To Connect Ot Access Mdb
How do you setup a conntection in ssis to connect to an access mdb? I have used DTS to upload access tables to sql and want to do the same in the new ssis services. I migrated the DTS package but the connection in created for the access mdb does not work in ssis and is shown to be be illegal. The migration did not post any errors when it migrated the package.
View Replies !
Connect To Microsoft Access Via Stored Procedure
Hi, I'm new to SQL server, still in the beginning stage of learning SQL Server. I'm here would like know, besides using the Connectivity from the DTS Designer to connect to different databases, is it possible to connect the database, i.e: Access via stored procedure? and how? Pls advise... I'm have been trying to look for the solution via a lot of SQL Server site, but fail to get what I want. What I'm trying to do is something like : First connect to the Database and Query the data, after that insert it into another database....
View Replies !
Access Denied-Connect "REMOTE" Via VB6 Script
When attempting to connect to SQL Server database installed at Windows 98 PC using a different workstation "REMOTE", always having ACCESS DENIED response. However, when using the same PC where the SQL Server database is installed "LOCAL" using the same script the query/connection was successful. Thanks
View Replies !
Using A Windows Application To Connect To An Online Access DB.
Hi all, I'm working on a project where I have to create a password protected form for teachers to submit reports about students. Originally I was going to use asp.net to create the form, but unfortunately the server I'm working with will not be granted asp functionality in the allotted timeframe. To solve this issue I have another idea. So I will ask a few questions to help with research before I begin coding. Is it possible to create a windows application that can connect to an access database hosted online? The windows app will need to be be able to update, insert, and select information from the database. There is only going to be about 30-40 people using this windows application once or twice a week. Thus I envision taking a password protected access database and dropping it onto a server. The windows application will have the online location of the database embeded into the code. (connection string) From there on out coding for the windows application should be the exact same as if the database was hosted locally on the computer, correct? Also, 1. What is a good way to check and make sure the computer is connected to the internet before my application tries to access the database? 2. Are there any drastic security issues with this process that I should think about. The database will only be password proected, and the windows application will take a username/password from the user and check it against a table before they are able to submit info to the database. I just need to be pointed in the right direction for this project. Thanks.
View Replies !
19/04/2002 + 23:00:00 = 17/04/2002 23:00:00 ??????
INSERT INTO StockTakeRecords SELECT Serial_Number, [Date] + [Time], Location FROM ImportedStockTakeRecords The date in the 'ImportedStockTakeRecords' is 19/04/2002. The Time in the 'ImportedStockTakeRecords' is 23:00:00. Still, the result in the StockTakeRecords is 17/04/2002 23:00:00. Does anyone know why, and how I can solve this? Regards Ruben
View Replies !
19/04/2002 + ' ' + 23:00:00 = 17/04/2002 23:00:00 ??????
INSERT INTO StockTakeRecords SELECT Serial_Number, [Date] + ' ' + [Time], Location FROM ImportedStockTakeRecords The date in the 'ImportedStockTakeRecords' is 19/04/2002. The Time in the 'ImportedStockTakeRecords' is 23:00:00. Still, the result in the StockTakeRecords is 17/04/2002 23:00:00. Does anyone know why, and how I can solve this? Regards Ruben
View Replies !
LIB][ConnectionOpen (Connect()).]SQL Server Does Not Exist Or Access Denied.
Okay Ive managed to confuse myself pretty good.I have an aspx page that attempts to connect to an SQLEXPRESS instance and add data to a table.Code as follows:beginning of page<%@ page aspcompat=true enablesessionstate=false language=javascript %><!--#include file="adojavas.inc"--><%@ Import Namespace="System.Data.OleDb" %>I have a toolfunction that when called upon runs this:if (xco >= -180 && xco <= 180 && yco >= -90 && yco <= 90) { status = "Can't connect to database."; // create connection to database var connection; connection = Server.CreateObject("ADODB.Connection"); connection.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OutdoorsExp;Data Source=\.pipeMSSQL$SQLEXPRESSsqlquery;"); status = "Can't access table."; // create recordset var recordset; recordset = Server.CreateObject("ADODB.Recordset"); recordset.Open("dbo.FishType", connection, adOpenDynamic); status = "Can't append new record, record set does not support AddNew."; // append record for clicked location if (recordset.Supports(0x01000400)) { status = "Can't append new record."; recordset.AddNew(); recordset.Fields("LongitudeI").Value = xco; recordset.Fields("LatitudeI").Value = yco; recordset.Update(); } recordset.Close(); // release the file connection.Close(); status = "Can't propagate changes to drawing."; // refresh drawing var document = mapserver.Document; var componentIndex = document.ComponentSet.ItemByName("FishTable Data Points Drawing"); if (componentIndex >= 0) { document.ComponentSet.Item(componentIndex).Refresh(); } status = xco.toString() + ", " + yco.toString(); }Unfortunately this gives me an error instead of transferring the value xco,yco to the SQL Tables LatitudeI/LongitudeI column.Ive tried many different connections strings, and have played myself into a state of confusion now :(.I get this error:[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.[COMException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]Ive tried named pipes and using IP to connect, and Im not 100% sure how to use SQL Server via "System.Data / System.Data.SqlClient".Any insights would be greatly welcomed!
View Replies !
SQL Server Does Not Exist Or Access Denied. ConnectionOpen(Connect())
The server that needs to be moved: SERVER A. The databases which need to be moved (present in SERVER A): DB-A, DB-B. The SERVER A machine is in X domain The target machines: Could be any of SERVER T1 This machine is in Y domain. While we are trying to create a new server instance of the Server A in SERVER T1, we are getting an error message as shown below: Reason: SQL server does not exist or access denied. (ConnectionOpen (Connect()) The server that needs to be moved SERVER A could not be mapped to SERVERT1. Problem: The tables concerned present in the SERVER A and the production server are same. These could be extracted to SERVERT1 using the SQL Enterprise Manager utility. Views and Stored Procedures concerned are only present in SERVER A. Problem lies in copying them. Manually doing so could pose great risks. What is the plausible work around for this?
View Replies !
Sql Server Does Not Exist Or Access Denied ConnectionOpen(connect())
I am trying to link SQL SE 2000 (Local server at head office) to MSDE 2000 (remote server). After creating the server group in local server, I try to register SQL server Computers (MSDE 2000) to place in it. But I received this error message. "SQL server registration failed because of the connect failure displayed below. Do you wish to Register anyway SQL Server does not exist or access denied connectionOpen(Connect()).'" I checked the setup for client and server utilities. Client Utility is TCP/IP Server Utility is Named pipe Please, help me. I don't know what went wrong here.
View Replies !
Can't Connect To SQL Server Express - Error Access Denied
Hi, I have been for years using the following code locally in native ASP (except different UID and PWD and file path for the msado15.dll) with SQL Server 2000 on XP to do my development and then I upload to the web site later (uses different objConn etc when live). I've now tried to use the same for SQL Server Express and Vista and always get the "does not exist or access denied" error ( 2147467259). I only want to use SQL Server locally from the machine I'm developing on as I upload the tested "ASP" code to the server which runs it. The SQL Server Browser is running and I have checked the Firewall exclusions for sqlservr.exe. Also I installed it with out Windows Logon (same as I had for SQL Server 2000). Any ideas, I tried using the Data Source=localhost instead of explicitly defing the path except that had no affect either, I also changed the rshstest_data.mbf to rshstest.mbf as well also to no affect as there appears a difference with this in 2000 and Express? Any suggestions would be appreciated, thanks.. <!-- METADATA TYPE="typelib" FILE="e:Program FilesCommon FilesSystemadomsado15.dll" --> <% on Error resume next objConn.Close Set objConn = Nothing Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.Open "Provider=SQLOLEDB;Persist Security Info=False;" & _ "User ID=xx;Password=xx;Initial Catalog=rshstest;" & _ "Initial File Name=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData shstest_data.mdf" ' "Data Source=localhost;" Dim strConnect strConnect = "Provider=SQLOLEDB;Persist Security Info=False;" & _ "Provider=SQLOLEDB;Persist Security Info=False;" & _ "User ID=xx;Password=xx;Initial Catalog=rshstest;" & _ "Initial File Name=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData shstest_data.mdf" ' "Data Source=localhost;"
View Replies !
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server Does Not Exist Or Access Denied.
I installed the SQL SERVER , Oracle client on my computer, and I can connect to remote SQL server,oracle databases using wizards , however when I try to run application iam getting this error. Server Error in '/shiva/datagrd' Application. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. 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.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Source Error: Line 40: //SqlDataAdapter da=new SqlDataAdapter("select * from sms",con);Line 41: DataSet ds=new DataSet();Line 42: da.Fill(ds,"tab");Line 43: DataGrid1.DataSource=ds.Tables[0].DefaultView;Line 44: DataGrid1.DataBind();Source File: e:shivadatagrdwebform1.aspx.cs Line: 42 Stack Trace: [OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.] System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) System.Data.OleDb.OleDbConnection.InitializeProvider() System.Data.OleDb.OleDbConnection.Open() System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) datagrd.WebForm1.Page_Load(Object sender, EventArgs e) in e:shivadatagrdwebform1.aspx.cs:42 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain() Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 please provide the solution.
View Replies !
|
TRACKER
