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.
I have a new computer with Vista Business and MS Access 2003. ALthough I'm not sure I have Access 2003 because it keeps blowing up with lugubrous error messages
1) Cannot find field '' 2) Out of Memory (I have 2GB and this happens with one dbs open)
These are the two functions I am using to calculate Years and Months that have passed since a specific date. It works. Its amazing.
Its a pain.
If the field that passes the value is empty, I get #Error in the field that calls these functions. Ive tried error correcting, but I still have the #Error showing.
Any help would be much appreciated.
'************************************************* ************ ' FUNCTION NAME: Age() ' ' PURPOSE: ' Calculates age in years from a specified date to today's date. ' ' INPUT PARAMETERS: ' StartDate: The beginning date (for example, a birth date). ' ' RETURN ' Age in years. ' '************************************************* ************ Function Age(varBirthDate As Variant) As Integer Dim varAge As Variant
If IsNull(varBirthDate) Then Age = 0: Exit Function
varAge = DateDiff("yyyy", varBirthDate, Now) If Date < DateSerial(Year(Now), Month(varBirthDate), _ Day(varBirthDate)) Then varAge = varAge - 1 End If Age = CInt(varAge) End Function
'************************************************* ************ ' FUNCTION NAME: AgeMonths() ' ' PURPOSE: ' Compliments the Age() function by calculating the number of months ' that have expired since the last month supplied by the specified date. ' If the specified date is a birthday, the function returns the number of ' months since the last birthday. ' ' INPUT PARAMETERS: ' StartDate: The beginning date (for example, a birthday). ' ' RETURN ' Months since the last birthday. '************************************************* ************ Function AgeMonths(ByVal startDate As String) As Integer
Dim tAge As Integer If IsNull(startDate) Then AgeMonths() = 0: Exit Function
tAge = (DateDiff("m", startDate, Now))
If (DatePart("d", startDate) > DatePart("d", Now)) Then tAge = tAge - 1 End If If tAge < 0 Then tAge = tAge + 1 End If
I am trying without any success to build a small stock control database.
very simply put I am the main site and I have many other sites. each site has a unique code. I have 4 products only. I want to have the total stock that I hold in my site and as I send stock to other sites I want the number to decrease from my site and be added to the other specified site.
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
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.
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)
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!
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.
*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?
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?
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.
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.
I have a database with a vast number of forms. I need to rename two forms but with no success. Once I rename the form, it just jumbs back to it's original name.I cannot save these files as well.
