Front/backend Access Application-----conversion To MSSQL--advice
Dec 16, 2005
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
Hi there,I sincerely hope that someone out there can help. I have twoinstances of the SQL 2000 Desktop Engine running. One is on my localmachine for development and the other is on another machine on ournetwork which is the production environment. I have built an Access2003 front end application which connects to this database. Thisworks fine locally, as you would expect. I successfully installed thedatabase on the production machine and am able to connect to it viaAccess 2003 (using the Data Link Properties window) and from thirdparty database manager software (similar to Enterprise Manager). I amnot able to to connect to the database via my application.I am using the "sa" account with a strong password. This is myconnection string:strConnection = "Provider=sqloledb;DataSource=server02;UserId=sa;Password=strong;Initial Catalog=Test"The error I'm getting is:"Connection cannot be used to perform this operation. It is eitherclosed or invalid in this context."The connection string is the only thing that changes in my code when Iswitch from my local to my production database. Is there some reasonthat I can't use the "sa" account in this fashion that I'm not awareof? I'd rather not use integrated security for simplicity's sake asthis is a small, internal application. Also, I would have thoughtthat if that was the issue, I couldn't use "sa" at all, even locally.I'm going to post to the Access group as well but thought someone heremight have some advice to offer as well.Thanks,Barb
If I'm using an Access front-end, and the data is on SQL Server being accesses via a linked table, and I create a query in Access, Where is all the work done?
I know access has the option of using a pass-through method, but if I do not use it, is Access processing the query locally? I plan on migrating several tables to SQL because the sizes are getting to large for Access and want to know if their will be a performance increase with out re-writing the queries in Access.
My company currently has about 20-25 Ms Access Database that they want toreplace the FE with .net and the BE on SQL.This will be done using Visual Studio 2005. Once the FE is converted to .netand the BE is SQL they all will be accessed through our intranet (sharepoint).I work in Ms Access and intermediate at VBA and just learing SQL through theENTERPRISE MANAGER SCREEN.I am just now looking at what Visual Studio 2005 is, but can some one tell mehow this will all connect?What is the typical route for this process?Ms access to SQL - upsizing wizard or SQL importing?????Ms Access FE to .net - summarize how this is done in visual studio (user facerebuilt) then placed on sharepoint?Can anyone sum this up?--Message posted via http://www.sqlmonster.com
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.
I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?
The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.
If anyones done anything like this before, I'd love to hear their views......!
This maybe belongs in the Data-Access Forum, but I'm not sure.
Is it generally a better idea to enforce things like unique constraints in the Database or the Webform? Say for example I want to make sure no duplicate Social Security Numbers are entered. Is it better to have an "If Exists" clause in my query, with a function to deal with it in the application or is it better to just fire the data to SQL Server and let the unique constraint on the dbase column deal with it? I then still have to have some code in my application to deal with the potential exisatance of that number, so is it a case of tomatoe, tomahtoe? If I understand things correctly, SQL server will return an error code if the piece of data does exist, and I will be able to parse the error code and display a message to the user.
Are there performance/coding issues involved? Best practices?
Hi,I'm new to SQL and databases in gnereal but am trying to source anapplication for windows that will sit on top of a small SQL database Ihave (created for local scout troup), I want an easy way to retrieve/add/view data from backend in nice menu-driven GUI format (no cmd-line).Can anyone recommend a useful app I could source to do thistask....thanks in advanceterry
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
Hi there,I have written an application which uses MS Access for it's database engine.Due to the large size which the database has become I have decided that itwould be sensible to use SQL Server with the application instead.I am an extreme SQL Server newbie so I am not really sure what I'm doingyet! I have successfully downloaded and installed the MS SQLDE 2000 andservice pack 3.What do I need to do next? Ideally I would like to convert the existingAccess database to MS SQL Server format. Also I would like to know if it ispossible to create an SQL Server database from scratch using a guienvironment similar to Access and if so which software (preferably free) doI need to achieve this?Many thanks,Clive.
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.
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> <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>
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.
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> <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>
We are trying desperately to get away from Access (ADP) and move on to something else. SQL Server 2005 is the engine and Access is the front end. We desperatley need another front end. We torn between FoxPro or going Web Bassed. Can whats a good application tool that will asssit me in making our Databases webbased??
Is there any front IDE for MS SQL as there is MySQL Front for MySQL. Iam looking for it so that it is easy to quickly add/edit/delete thedata from the existing table. Please prode the Download URL is there isany.Regards.
I'd like to get some help on this. I'm new to SQL Server but am keen to learn it. I have SQL Server 2000 SP4 running and wonder what kind of front end application is best suited for SQL Server 2000.
I am helping a friend, current database Paradox 7.x, custom app as I am sure they all are. I wanted to convert this DB to SQL or Access(MS)I do not have Access, but I have SQL 2000. I used the import/ convert tools within SQL, looked good to me, no errors etc.
I thought life was good. Now try to view, look at the data the way users could. In the SQL manager, I can see tables, and stored procs, and lotsa cool stuff. I try and install SQL client on a PC to view. It basically extends the Admin tools to the deskop PC. How do I use an SQL conversion to realize the needs of the user interface. I think I need what is called a front end. You can stop laughing now.....
Anyway what direction should I take to update this antique, no support Paradox DB.
Hi All, From front end application, I execute a SQL `exec sp_password oldpassword,newpassword and I got error message : "Error code# 15002 :[Visigenics] [ODBC MS SQL Server 6 Driver] [MS SQL Server] The procedure `sp_password` cannot be executed from within a transaction.". If I logged in directly to SQL 6.5 without going thru the front-end application then I could change password successfully. Has anyone tried to change password FROM a front-end application? Our shop uses Omnis 7.3.6 as front end and SQL 6.5 as back end. TIA. David Nguyen
there is a Crystal Report run from the Front end Application. The DB used here is SQL Server 2008. I need to know the Stored Procedure used by the report that is been run from the front end. . How shall I do it?
in create a new software application using C# as front end and database with sql. how to connect SQL server with my front end application.dot net training in chennai <a href="http://www.fita.in/dot-net-training-in-chennai/">Dot Net Training in Chennai</a>
I created a SQl Server 7 database and used an Access 97 frontend on the same computer. The Access frontend uses linked tables to reach the SQl server. The security setup I have is based on the NT Domain Accounts and whatever group I have them in (example: Mr. D is in Marketing therefore he is in a NT Domain Group called "SQL_MARKETING"...the SQL Server recognizes that everyone in this group has access to whatever role I have designated for it in the database"). I can access the linked tables fine on my computer at my desk or at the server. Another person with the exact same administration rights as me can get to the server through the network neigborhood or by mapping..and they can access the Access frontend with no problem from their computer. But when they try to open a linked table they recieve an error message that the ODBC connection has failed. We are all running Access 97 service pack 2 and another person and me have the exact same administration..but only I am able to access the Access linked SQL tables but not him.I tried deleting the ODBC system dsn on the server and the linked tables and then re-adding them and the ODBC link but to no avail. Does anyone know why this would be occuring? Thank You in advance. and the
We are entertaining the idea of moving our data to a cloud and I need to know how to convert my Access backend DB to SQL. which version of SQL will I need to use to do the conversion and will also work in a cloud environment.
I have a database with a sql backend and an access frontend. I put it in a folder on the network for users to access. First, they were getting errors like not being able to access the database if someone else had it open and not being able to perform certain functionality. It was suggested that i put a mde on each of the users pc. i gave them all access to their own front ends on the folder because they use different pcs. this solved the problem. however, i need a way to perform updates without manually creating mdes for every user. i used to work for a co that there was one mde was put into a folder on the network with a config file. The users access the database through CITRIX. Whenever they accessed the shortcut on the server, it created the users their own front end. Whenever we need to provide updates, we simply made a new mde from the mdb and whenever the user accessed the database again, they would have the latest mde and it would created an update frontend for the user. Does anyone know of such an utility or know what I am referring too? I encounted several suggestions like a batch file; but they refer to putting the file and the mde on each users pc. This is not how i'm set up. Thanks
Hello. I have an Access 97 frontend and a SQL 7 backend. When I try to open up the linked SQL tables in the frontend I recieve the error " This recordset is not updatable" from Access. I have the permissions in SQL set at this point to public having all permissions but it still does not change my problem. Does anyone know the answer?
Ok, little frustrated hence the excessive exclamation points.
I'm designing a database in access to use a sql backend. Table structures are setup and am creating a view to join multiple tables together for data entry purposes. Tables are joined properly but when I add more than one join, ie adding more than two tables, I can't add records!?!
I checked the joins, it's not the table, since if I add one table its ok, remove table, add another table and that works, but when I add both tables together, it prevents me from adding records!
And another thing, when I have just two tables joined together and add a record, it comes up with this save changes, drop changes or cancel thingie!!! WTF!!!?
Is there something simple i'm missing or is it my table structure????
Hi, this is my first post (hopefully of many) on this board. Just wanted to say a quick hello before I dive into my question. :)
As the title suggested, I have to develop a MS Access form app (yes, it has to be Access - I know it sucks) that will post and query data to and from a remote SQL Server db. While I have no problem linking the two through the default ODBC drivers, my question is security. Some (actually most all) of the data being passed back and forth is sensative information, and I would like to know the best way to keep it safe.
If anybody has any suggestions, instructions, or can redirect me to a good FAQ site on the subject, I would be most appreciative. I have already done a search on these forums for an answer, but have sadly come up short.
We are new Access users and trying to take our database live...here are some of the problems we're facing.
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend: 1.) When we run our queries, multiple copies of the same record are displayed (this is wrong). 2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also, if anyone knows any websites or resources that can help us understand what things like Expr1 mean (a reference) that would be helpful too!
We are not a very advanced group of access users and are trying to split our database for a research study...here is our question:
When we split our Access database into a Backend and Frontends, a couple of strange things happened in the Frontend: 1.) When we run our queries, multiple copies of the same record are displayed (this is wrong). 2.) When we look at those queries in design view, the field names are preceded by Expr1, Expr2, Expr3, etc.
We have a large number of queries. How can we fix this problem?
Also if anyone has a good source for trouble shooting this or simmilar difficulties we'd appriciate any spots or advice.
Hello all,I've been recruited to assist in diagnosing and fixing a performance problemon an application we have running on SQL Server 7.The application itself is third party software, so we can't get at thesource code. It's a Client Management system, where consultants all overthecountry track their client meetings, results, action plans, etc. , and hasapparently been problematic for a long time now. I came into thisinvestigationin mid-stream, but here's the situation as I understand it:We have users reporting it's slow, with no discernable pattern with respectto what part of the application they're using or now particular time of day.I am told that it doesn't appear to be a bandwith or computer resourceproblem. They apparently added two app servers a year or so ago, whichtemporarilyimproved the performance. We're using a nominal percentage of CPU andmemory.There are three large tables (approx 8 million rows) that are queried often,as users click to see their calendar of appointments or review past meetingswith a client, etc. The activity on these tables is over 90% reads(SELECTS) with about 10% INSERTS/UPDATES. We have attempted to run the IndexAnalyzer Wizard twicebut so far it just seems to hang (it could be that the workload file is toobig?) . So, what we're doing now is isolating the SELECT statements thattake a long time to run and manually comparing them to the indexes thatexist on these large tables. Since we can't alter the SQL source code,we're trying to alter the indexes to improve performance.What I would like to know is, is there a good way to get benchmarkmeasurements so we can explicitly measure any performance changes? Also, doyou thinkwe're going about this the right way, or is there some other avenue we couldbe looking at to improve performance?I recognize that performance questions are tricky to post/answer in anewsgroup, because usually you need more information than is provided. Theproblem isthat this is a high profile investigation (they're hauling us into meetingsevery two days to report our progress) and I need to be able to convincinglystate that we have either improved performance by X% , or that it is theapplication itself that's the problem and we're stuck with it.Any thoughts would be deeply appreciated.Thanks and best regards,Steve
4 Layered Web Application for Inserting data into a database using sql server as the back end and a web form as the front end using C# . Can someone provide with code as I am new to this architecture and framework. Better send email. Thanks In Advance, A New Bie
