Access App Kills SQL SERVER After Upsizing

Aug 11, 2005

HI.
Your help is very appreciated.

*I upsized ACCESS 2002 (tables only) to use SQL SERVER 7.0 as backend.
(linked tables).
* Almost all the ACCESS forms are bound type forms.

Now, I have two problems:
1. Other applications (not ACCESS ones) that use different dbs on the same SQL SERVER , are slowed down / halted.
2. My SQL SERVER license is limited to 8 "user counts". When there are 8 instances of the ACCESS app, any additional connection request is refused (from any app).
(I can't afford buying more "user counts").


I suspect that beacuse the forms are bound, it hold connection open as long as the form is used.
Also, each ACCESS app instance, opens its own connection.

The solution that I see, is to use unbound forms(disconnected), and opening the connection in code using connection pooling.
Would you recommend on this solution?
do you have any suggestions?

Thanks a lot
Roy

View Replies


ADVERTISEMENT

General :: Upsizing Access Database To SQL Server Error

Nov 4, 2013

I am trying to upsize the access database to sql server ,i have an odbc connection and when I try to move the tables,all the data is moving except one table "tblproductinfo" . I have attached the table data and error.

View 1 Replies View Related

Access Upsizing Wizard Help

Aug 19, 2005

I'm just after getting my web site designed and I want upsize my present database which is MS Access to a MSSQL server can anyone help? I'm totally new to databases so I need step by step help!

View 1 Replies View Related

Access Upsizing Question

Nov 28, 2007

Hi,

I get some strange index when I upszing a Access to SQL Server

This is part of report

Loading objects into database...
Analyzing metadata...
Preparing schema TESTTAB.dbo...
Preparing table TESTTAB.dbo.[Conversion Errors]...
Preparing table TESTTAB.dbo.[MSysCompactError]...
Preparing table TESTTAB.dbo.[tblAmpIDNum]...
Preparing index TESTTAB.dbo.[tblAmpIDNum].[tblAmpIDNum$IDNumber]...
Preparing table TESTTAB.dbo.[tblAmpSurgery]...
Preparing index TESTTAB.dbo.[tblAmpSurgery].[tblAmpSurgery${B1914EFC-E9F2-4DB8-B238-6D45DA5F1D4B}]...
Preparing index TESTTAB.dbo.[tblAmpSurgery].[tblAmpSurgery${DB7FC1FE-6735-42D8-96C0-B3C03372F49E}]...
Preparing index TESTTAB.dbo.[tblAmpSurgery].[tblAmpSurgery$AmpLevelID]...
Preparing index TESTTAB.dbo.[tblAmpSurgery].[tblAmpSurgery$AmpSurgeryID]...

What the number B1914EFC-E9F2-4DB8-B238-6D45DA5F1D4B mean?

Thanks

Mark

View 2 Replies View Related

Upsizing Access Question

Jun 30, 2007

Greetings:

I have been using front-end back-end database, which consists of three .mde files connected to an .mdb backend on a Windows Server 2003 Windows XP Professional network. The database is a flat-file. Because of problems associated with record corruption, related to multiple users and memo fields, I am looking to upsize my backend to MySQL or SQL Server both of which I know little about. Additionally I am hoping that upsizing will help alleviate the slowing down I am currently experiencing as the back end grows.

My question is am I looking in the right direction will either of these correct my problems and if so which of the two should I use.

Mickey

View 3 Replies View Related

Unable To See Relationship Diagram In Database After Upsizing From Access 2003 To SQL

Nov 9, 2011

I have upsized from Access 2003 to SQL Server 2008 R2 using upsizing wizard. Everything works fine. But I don't see relationship in SQL SErver 2008 R2 if I go to database. But I set relationship in Access 2003 before upsizing it.

Amso I don't see relationship (diagram) in Access 2003 , which I was able to see before.

So do I need to again recreate the relationship amongst the table in Access 2003 Or SQL SErver 2008 R2 ? I thought, if you link tables, everything should be taken care but i don't see relationship structure any more.

View 1 Replies View Related

Upsizing

Mar 5, 2007

I have a MS Access 2003 database that is getting corrupted frequently. I am following all the rules to avoid this problem but it keeps boring me with "Access can´t recognize this database format" and I lose all my data.
I´ve sent the db to experts to try to recover it and they told me that it was impossible. So, I´m planning to change my Backend (BE) and keep my Access Frontend (FE), because I like Access very much.
My question: what is the best solution for BE?
SQL Server? Firebird? another one?
I hope by changing the BE I won´t have problems anymore...
Thanks in advance
Renato

View 8 Replies View Related

Advice On Upsizing To SQL

Sep 25, 2005

Would anyone be willing to have a discussion about upsizing a large split database to SQL? I've managed the split using the wizard and it went very well. The problem is the performance, the form is really slow on opening and as it scrolls through records.

All my queries are saved. I would imagine it's the way I've built the form and base queries that's causing the problem and wondered if anyone would be willing to have a look at it for me and see if they can make any suggestions.

I'm struggling blind with this one.

Thanks.

View 14 Replies View Related

Upsizing Wirard

Mar 17, 2006

After Loading MSDE and running the upsize wizard should I be able to create an ODBC link to the data using a SQL driver?

View 5 Replies View Related

Upsizing Wizard

Jul 27, 2007

Ok......

It's much as I thought it would be. MS Access is limited for a mid size database application. I have developed this mid size database for the medical clinic I work for. I didn't want to do it, but they saw some of my personal projects I created and pretty much told me to come up with something.

Well, the good news is, the database itself works exactly as designed, meaning it does its job very well. The bad news, and I knew it would happen, is instability. When I first started creating this program, I had no idea how many people would be using it at once. That, and after reading through these forums for the last few months, I realized that Access wasn't the puppy to be creating this database in. As it stands, about 130 - 150 systems access the database (about 3 times more than I originally forcasted.)

I'm sure those of you who have done something similar knows the next thing I'm about to say...... yep..... mysterious run-time errors - locked database from time to time - owc (office web component) errors...... etc. etc. These errors pretty much happen during peak hours.

I am pretty positive it's because access wasn't designed to handle such a load..... which led me to a researching a resolution. The answer that may be my savior is the upsizing wizard included with ms access. We do have MS SQL server running on a server...... I was just curious if anybody's ever used the upsizing wizard; either that, or has anyone ported an access project to sql. (This is a big program, and would hate to have to reprogram)

View 2 Replies View Related

Victim Of It's Own Success; Upsizing Question

Mar 20, 2006

Hi,

I have developed a couple of database applications. This started as a small thing but now is growing to the point where I've got to make some decisions and would appreciate some guidance.

Presently:

Front end: 4 Different GUI's as local front ends.
Back end: Intranet network share on Win2k desktop (limited by O/S to 10 connections at one time)

Going forward, about 30 users will have access to the front ends and will all need concurrent access to the back end data. At some point, I would like to generate web pages from the data in the back end database and have the back end machine also be the web server for this.

So, what are my "best" options for the back end?
I was thinking of getting Win 2k3 Server running SQL server to drive the database.

The ability to integrate security with existing infrastructure would definitely be a plus, which I believe Win 2k3 can do.

I don't know very much about Win 2k3 or SQL server, but am willing to invest in learning the ins and outs as long as I can get it up and running with the same functionality I have now (with relative ease) and scale it as my capabilities with the OS and SQL server increase.

To be honest though, I am a little scared about how much I'll have to change my existing applications in order to make it work.

Your suggestions, comments, experiences most welcome and valued.

Thanks,

Al

View 1 Replies View Related

Upsizing Creates Huge MSSQL

Feb 1, 2005

I am upgrading a .mdb to MSSQL. The .mdb is 17MB, but the resulting MSSQL is 72MB. Tried using both the Access Upsizing Wizard and Enterprise Manager DTS. I have done this a number of times before, but never ran into this problem. Any ideas what coule be going on, and how to fix it?

View 1 Replies View Related

Upsizing Database To SQL - Tables Being Skipped

Feb 3, 2014

I am attempting to upsize an Access database to SQL 2005 Server. All of my tables are "skipped" why?

View 1 Replies View Related

Access FE SQL Server BE

Dec 5, 2007

We're currently looking at changing a couple of my more complex Access 2003 databases to Access Front End with a SQL Server Back End. This should give us better stability and reduce network traffic loads. I was looking at the Access help file on Upsizing and came across the following paragraph. On face value to a novice like me this would seem the easy option - but is it?

"Create an Access database front-end to an SQL Server database back-end so that you can create a front-end/back-end application.

This approach requires very little application modification since the code is still using the Jet database engine. "

Would anyone recommend this approach?

View 4 Replies View Related

SQL Server To Access

Feb 15, 2006

I am importing an Excel file into a table in SQL Server that was created by an AS400 program.

I linked this table in MS Access. The date fields from the SQL Server table are split out. DSREQM is the month and day. DSREQY is the year. When opening the table it looks like this:

DSREQM DSREQY
215 6

I need to be able to convert this is a field to 2/15/06. However I cannot get this to work. For some reason when I execute the query it comes out as 8/02. Here is what I have:

DelDate: Format([DSREQM],"mdd") & "/" & Format([DSREQY],"y")

Any ideas?

View 5 Replies View Related

Access Using Jet And SQL Server With ADO

Aug 29, 2007

Hi

I am attempting to write an application, using Access2003 as the front end. The Backend May be either Jet (ie an MDB), or SQL Server.

I am trying to make it so that the same code works regardless of the back end.

My approach has been to set the forms recordset to an ADO recordset like so:

Set Rs = New ADODB.Recordset
'Rs.CursorLocation = adUseClient
Rs.CursorLocation = adUseServer
Rs.Open strSQL, gcn, adOpenDynamic, adLockOptimistic
'
Set Me.Recordset = Rs

Anyone done this, and willing to sahre information/pitfalls.

First problem I am encountering is that Jet only seems to like .CursorLocation = adUseClient, and SQL Server only seemns to like .CursorLocation = adUseServer

View 6 Replies View Related

Installing Access On Server

Apr 26, 2005

Hi to everyone
Im not sure which thread i should be in, but here goes :confused: i want to create a members database for members on my forum with HTML!
Ive been told to make the database with ACCESS ive played for 2 days and im now acctually getting somewhere, i would like members to interacte with it ie....add there own details to it.
How do i do this and how do i get it to my server, well i did upload a tester but no one could see it and its says they need to have MS installed or something like that :eek:

What do i do here??
Thanks for ANY help

View 6 Replies View Related

MS Access On A CITRIS Server ?

Sep 7, 2005

I'm trying to help someone with Access on a CITRIS Server. I'm working with them VIA email. They say they're having problems communicating with printers and would like to utilize their email with Access. Can someone fill me in on common problems using Access with CITRIS Server as far as printers or email or anything else?

To be perfectly honest, I know nothing about CITRIS servers. Is CITRIS a hardware system or computer type of is it an operating system???

View 3 Replies View Related

Access Projects With SQL Server

Oct 26, 2006

I need to be able to add a feature to an Access project that will select a group of records based on an area code( user will have to be able to select a desired number like 100, or 1000), mark those as selected, and create a single csv file...The area codes are stored in the SQL SERVER database...right now they just export groups of #'s based on area code to excel select the amount they want and cut and paste into a flat file for another application to use...they would like to be able to select a number from a list box or type it in...like 100 ( which means take a 100 #'s from all different area codes) then have them automatically noted in database as being selected..and then export all selections to one csv file... should I do part of the work in the Sql Server behind the scenes..the format of the csv file also needs to be specific in that the first row is the columns separated by commas and then the data follows all text fields enclosed by qoutes..
Thanks for anyone's help ..Im very inexperienced with VB and Access

View 1 Replies View Related

MS Access And Sql Server Permissions

May 8, 2007

I have created an adp, and have created stored procedures for all of my forms. In the SQL Server database I have given my users permissions as db_datareader and db_datawriter, however they were unable to open any of my forms because they didn’t have execute permissions on my stored procedures. I finally had to give them permissions as db_owner so they could use my forms. Do I have to give them db_owner permissions to execute my stored procedures? I know I can go in and assign them execute permissions for my stored procedures, but there has to be a better way. Does anyone know how to give users execute permissions on stored procedures without having to give them db_owner permissions or having to grant execute permissions for each stored procedure individually?

I am still in the development phase of my project and having to assign users or groups with each stored procedure I create could really be a pain. Can someone please help me?

View 2 Replies View Related

SQL Server 2000 With MS Access

Nov 7, 2003

I am accessing a SQL Server 2000 database from MS Access. While I can view the tables and can add records, I cannot edit or change them. How do I get around this?

View 10 Replies View Related

Access / VBA / SQL Server Question:

Aug 29, 2005

I am trying to build a form to make deletes from our sql database easier for some of my coworkers.

They have a combo box that they select a patient name from, then based on that patient id, they have a second combo box that shows all appointments that patient has been to. All right so far, yes?

I want, after the first two selections are done and we have a patient and an appointment selected, to have a textbox that returns the count of the number of medical documents based on that appointment id.

I am trying to use a text box for this.
Code: "select count(appt_id) from template_audit where appt_id = " & Me.cboAppointment

I don't know what property of a text box to use to return a single sql statement into it. Anyone have any ideas?

Thanks.

View 2 Replies View Related

Migrate Access To Sql Server

Sep 26, 2005

The database that I have on MS Access has tables, queries, forms (VB code), reports and functions (VB). I could migrate the tables and queries through the Enterprise Manager. I want to have the forms and reports in html. Is there any software tool that can help me with over all migration?

View 1 Replies View Related

Ms Access Security On Server

Jan 15, 2006

I've create an ms access security workgroup and put .mdb and .mdw file to a folder under any acount on a server. When these files in my local machine it work properly. But when these files on server I myself cannot open .mdb file.
How to make ms access security on a server ?

View 1 Replies View Related

Difference Between MS Access And SQL Server

Feb 16, 2006

I know and have worked with MS Access for over 3 years now.
Just would like to know if there is very much difference between MS Access and SQL Server. Are they similiar or are they completely different .
In terms of designing forms, reports, sql.

Appreciate some feed back and also where I could view or look at sql server.

View 1 Replies View Related

Connection Between SQL Server And MS Access

Jun 17, 2006

Hi :

I have a question of the connection between SQL Server and MS Access.

I have a project, which is using SQL Server as backend, we use MS Access forms as the frontend.

Firstly, I created the datbase (db1) in the SQL Server, and create a few tables in db1. And then, I create a few forms in the MS Access, I used ODBC connection to link tables from SQL Server to MS Access, it is working fine. Those forms can access the linked tables.

After it is done, I put the MS Access database in the sharing drive, many people can share the files in this sharing drive.

When I go to my associate's computer, and then open the MS Access database in the sharing drive. The forms is working, but, it cannot access to linked tables from SQL Server.

I go back my computer again and then open the MS Access database, it is working fine.

What's the problem with it?
Does anybody need to install SQL Server software and also have db1 database in SQL Server and the path?

I want any user can access the linked tables from SQL Server, but I do NOT want any user can open the SQL Server and open the tables directly. I just want the users can access the linked tables through MS Access Forms only. Because I will make security in the MS Access. So, I don't let the users modify/open the tables in the MS Access, the users must access the tables via the forms only.

Please tell me the steps how to solve it.

Please help.

Thanks.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved