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 RepliesHi guys!
Is it possible to connect both access and mssql tables to a access database?
A costumer has asked me to update a small part of a mssql (Web) from a access database.
In order to make my application, can I create to tables in access, and "replace" these with sql tables?
Or am I way of on this?
Hi,
My access database's tables are currently linked to an excel document. My question is, how do I move my database to the network? Currently when I move the the database to the network no one can open my tables because the link goes to my documents on my computer which they can't access on the network.
So how do I move the database and maintain the link between the database and my tables?
Thanks in advance,
Ryan Smith
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.
I'm struggling blind with this one.
Thanks.
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 RelatedOk......
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)
Hi,
I have a huge databse with a form, showing record info. If I use a search button to search the databse it takes ages to find the record.
The quickest way is to find the record by hand, find the record number, copy that, and paste it into the form and hit enter.
Does anyone have any ideas how i can speed thing change over up.
Thanks
Alex
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 RelatedHi,
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
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
One of our key customers (GE) has demanded that we begin to submit our estimates in their transmission 160 character format.
Basically it it a txt file that looks like this all on one line.
1ABCDWXYZ0502EST TEST123456T050208654321C 00010851803098518031 000000E 0000000000000D CAR SHOPPED AS CLEAN-PROCEED WITH
Only a couple of those characters are in my database and will change. Other than that items will continously be the same. The items that are optional and we don't have not only have to be blank but have to have spaces there instead...not 0's.
How do I set up a query to kick all of this out? I tried something like:
GEString:2UPRR YYMM00&""&WorkOrderID yada, yada, yada
but it didn't work....any ideas? I also need the YYMM to be the current year/month and I have to pull the decimals out of the estimated billing costs.
Is there a better way to do this? As you can imagine...typing 5+ pages of this for each job would be a NIGHTMARE!
I just recently noticed that the database that I have been working on has changed from around 60 mb to 430 mb. I added a few queries and forms. But could that really add to the size that much?
View 9 Replies View RelatedI inherited a database from someone else which has about 9000 records.
When sorted, one can see that the AutoNumbering starts at about 72K...then after a while there is a jump to about 134K...then after some more records the AutoNumber suddenly goes up to about 396K.
Does anyone know the most likely reason(s) that the numbering is like this?
Thanks in advance.
I have a form that contains a field for file box numbers, and a field for two different box numbers. I have a check box set up for the data entry person to check when the box if full of files. I need the the box numbers to increment by 1 for the two different box colors when the check box is = yes. Can this be done?
View 4 Replies View Relatedi've just delivered my program to my client and there are 2 MAJOR bugs.
bug 1: the first time program is opened, everything works fine. it goes to the switchboard, etc., everything's cool. however, if he closes the program (i think he closed it using the red X) and tries to get back in, only one item on the switchboard shows up from that point on. the thing is, this problem DOESN'T HAPPEN ON MY COMPUTER so i have no way of even attempting to figure this out. the way i have my program set up is i have an interface and a BE and an .mdw file all in the same folder. i've created a shortcut that uses a workgroup switcher so that he can open the program and use it w/the .mdw file.
i have no idea what could possibly be causing this to happen. i would think that the switchboard would open correctly every time.
bug 2: this one does happen on my computer.
setup: i have frmCustomers (outer form) with sfrmJobs (subform) with a subform inside of that named sfrmFinances. Everything in frmCustomers works fine, and everything in sfrmJobs works fine. The primary key (JobID) in sfrmJobs is entered manually. After the JobID is entered, sfrmFinances is accessible. sfrmFinances' control source is qryFinances, which has a few fields from tblJobs (control source for sfrmJobs). frmCustomers is linked to sfrmJobs by CustomerID and sfrmJobs is linked to sfrmFinances by JobID.
problem: a new customer is entered in frmCustomers. a job number is entered for the new job in sfrmJobs (JobID). the user then attempts to update a field (any field, but for example, ContractPrice) and a window pops up and says, "Field cannot be updated". you click ok and it pops up again, at least 10x in a row. then it finally lets you enter data, but when you try to save the record, it pops up with another window that says,
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
excuse me?? how could there possibly be duplicate data??? the funny thing is tho, when the user closes the form, and then opens it back up and tries to enter financial data w/the same customer, it allows it.
???????
The first bug is more important than the second one, but any advice or ideas on either are EXTREMELY appreciated. thank you very much in advance.
-Jason
I try to get an ASP file work with Access database and I get this error:
Microsoft OLE DB Provider for ODBC Drivers- Error '80004005'
Specified driver could not be loaded due to system error 126 (Microsoft Access Driver (*.mdb)).
I tried everything and nothing - and the configuration of this driver doesn't work...
How do I fix this driver??
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
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
I am attempting to upsize an Access database to SQL 2005 Server. All of my tables are "skipped" why?
View 1 Replies View RelatedHi everybody,
I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend.
I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified frontend.
This is when the problems started; We had two different sets of forms for accessing one of our databases systems - the log system, one is the original dynaset based form, and the other is a newer set which uses snapshot views and preforms updates via queries. Nobody uses the old dyanset system apart from my boss and one of the administrative team as they have things on that window which they need to see. About 30 minutes into the release of the new database the system frooze up on my bosses computer and nobody could create a new log (the server was timing out). I assumed this had something to do with the old dynaset's creating locks on the table.
I offlined the database and kicked everybody out of the front end, turned it on again and tied again, this time banning everybody from the dynaset system. Within 10 minutes another computer frooze up, again with a timeout on the insert query. I discovered that after you had added a new log to the database it would timeout on all subquent additions (something it hadn't done during testing) . Further investigatiion showed it was the snapshot list window causing the error, so coded the add log window to close the list before preforming the insert query and then reopen it afterwards. This allowed my machine to make multible additions without flaw. So I released a new client to everybody. 15 minutes later it was timing out again, but this time there you could not even make one new record.
I checked for locks on the table though the server management table and couldn't find any for the Log table. I have restarted the SQL server box and with no avil. So I reverted our backend to the access mdb file and told people to use the old client.
I am at a complete lose to why this is happening, if anybody has had any expericences like this or knows the cause please tell me.
Some information on the database in question.
It was made as an access 2 database all intergrated into one file, then it was seperated into two files (frontend and backend). Upgraded to Access 97, then to 2000 before this final update to MSSQL 2000.
The log system has two main tables. The first is the log title / info table which links (one to many) to a log entry table. This problem only occurs on the main log table and does not appear to be reoccurring anywhere else within the database. The main log table has just under 18,000 rows in it.
Thank you in advance for any help,
Dom
Hi all,I'm in a bit of a dilemma here. We currently have an mdb which includes a form and 4 tables. The entire mdb is around 800 megs. What we used to do is distribute this mdb to users in completely different locations on a CD, but now it has become to large for a cd to hold. Our manager wants us to figure out a way to distribute this mdb using only 1 cd, without zipping it. How would i go about doing that? Is there currently an installer, that can download the database from our ftp site and then insert the tables, or is there any other easier way i can get this done? P.s. The database will likely get larger and larger every yearThanks in advance
View 14 Replies View RelatedHere is the scenario:
I have a bunch of people with laptops who go on reviews throughout the state and leave reports on site with the facilities they review. They go out in multiple person teams and work together on reviews. Currently, each person types out their own citations in MS Word on their own computer, saves them to a flash drive and then the facilitator compiles them all together and prints out a report. Then the facilitator comes back to the office, provides their support staff a copy of the report, and then the support staff finalizes it and sends out a letter.
Unfortunately, none of our laptops have Internet capability out in the field.
My idea is to have a multi-user split main database that is stored at our main office. This is for the support staff to store all of the reports. Then I propose to have a local copy of the database (much smaller) on each of the laptops.
Here are my questions:
Lets say that 3 people are out on review together. They each have their entry form on their own computer. The form will be sending information to a few different tables. How can they easily export out their results so that the facilitator could easily import them into their database on their computer.
If all of that were to work somehow, how could the facilitator easily export out the review information tables so that the support staff could import them into the main database.
One of the many stumbling blocks I see relates to Primary keys. The main database will have a main table with a PK of 2008.100 for example. While out on review the reviewers will not be able to use the naming conventions of the main database PK. They might use something like the City name and date (Pensacola02222008). Could that PK be automatically switched into the other format upon import?
Let me know ya'lls thoughts. I have been trying to get my head around this for a few days and have not been able to come up with a feasible solution.
Thanks in advance.
Guys, my database have 7 fields and 690 thousands of lines.
When I resort, for exampe by date field, the database on my P4-3.2 with 1gb memory, on Access 2003 with XPpro freezes and does not respond at all!!!
On my old P3-866 512mb with Win2k it was working! After 3 minutes the computer was getting back.... But now.... I waited for 20 minutes. The access doesnt load not a CPU, and no IO readswrites are done - it simply stops responding.
What can we do with it? Why the perfomance is SO LOW?!
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 RelatedI'd read a few places that embedding pics wasnt the greatest of ideas, but I thought that 1 JPG embedded onto 2 forms would be ok and would prevent the pic from being deleted or moved. (Theyre always screwing with the server here.) But it's making my DB huge.
No pics embedded
Size: 808kB
1 Pic Embedded into 2 forms
Pic Size: 156k JPG
DB Size: 135 MB
Is this normal? I could understand a 156k pic adding somewhere around 156k to my DB, but 134 megs? Anyone have any insight?