Access 2000 & MSSQL 2000 Multiuser Problems

Aug 20, 2007

Hi 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

View Replies


ADVERTISEMENT

Access 2000 Security - Authenticate With Windows 2000 Server Credentials

Oct 24, 2005

Hi

I have a database that currently has security in place so that users input their own usernames and passwords to access the database. The users have recently been added to a windows 2000 server and I want to be able to use their windows 2000 server logon credentials to provide them with automatic access to the database. Is there anyone out there who knows how to set this up? It is a rather urgent request if you could get back to me either on this forum or via email

chris@ctbjs.co.uk

many thanks

Chris

View 1 Replies View Related

Opening & Closing Excel 2000 Workbooks From Access 2000

Jun 2, 2006

I am opening and closing a series of Excel 2000 Workbooks using Access 2000 VBA and want this sequence to be able to complete without any human intervention.

However, there are 2 instances when this stops and waits for a human option to be selected:

1. When the spreadsheet is password protected
2. When the spreadsheet has automatic links I get the message:

"The Workbook you opened contains automatic links to information in another workbook. Do you want to update this workbook with changes made to the other workbook?"

How can I code it so that in situation 1 it skips this file and in situation 2 it automatically defaults to do not update?

Any help most appreciated.

Dalien51

View 1 Replies View Related

Link/Transfer Data From Access 2000 To Excel 2000

Mar 27, 2006

If I have a report in MS Access 2000 generated based on the criteria selected of a project with work order "9999" with the labor costs, materials costs and the Totals of each crew1, crew2,... and I would like to have those expenditures populated in corresponding cel in Excel for each crew, HOW would I do it?

To think it out loud, could I create a button on a form, so when I select the criteria for the work order, and when I click the button, it should refresh/update the Exel file with the new data...?? How do I write VBA code for that...?

Please help...Thank you so much....

View 3 Replies View Related

Replication Between SQL Server 2000 And MS Access 2000.

Nov 15, 2006

Is it possible to have the master datebase in SQL Server, the replica in Access format and still can sychronize them?

Thank you in advance.

View 3 Replies View Related

Access 2000

Dec 20, 2007

can an Access 2000 Db be accessed over the internet??

If so, is this done often???

View 2 Replies View Related

Access 2000

Jan 25, 2008

Hi all,

At the moment the company Im with uses Access 2000, however ive been told by a few people that Access 2000 had alot of issues and it would be worth informing the company to upgrade to 2003 asap. Is there any major concerns with 2000? Some of the databases currently being used are very large and complex. Any chance data could be lost/displayed incorrectly? etc

Thanks for any input

View 8 Replies View Related

MS Access 2000

Jul 22, 2003

I'm new to MS Access. I want to know about compressed/reindexed databases.

What happens when a database is compressed?
What happens when a database is reindexed?
Why is it important to periodically compress and reindex a database?

I have records in the database that I need to pull up by local time zones, what would be the most effective way to do this?

View 2 Replies View Related

Need Access 2000 Help

May 12, 2006

I have a form that submits to an access database which has auto number enabled.. Trouble is that some of the submissions are missing and the database skips an ID number.

View 1 Replies View Related

Access 2000 To 2002

Jul 8, 2005

I need to develop an application that works in 2002. I tried to take a 2002 mdb, convert in 2002 to 2000 and try it on my machine at home. It didn't work and I couldn't find the missing reference. I then took an mdb that was originally a 2000 format, converted it to 2002 and it worked fine. So what I want to do is develop the app in 2000 and just convert it over to 2002. This allows me to work on it from home which is pretty important since my deadline is creeping closer. Am I ok doing this or am I courting disaster? Or is there another way of doing this?

scratch

View 2 Replies View Related

Users In Access 2000

Aug 16, 2005

Is there a way in Access 2000 that I can set up a users table? Basic info:

Name
Phone Number
Fax Number

Then every time the database is opened they are prompted for their name?

I have a database that has a Fax Cover Sheet within the DB, when they click the Fax Cover Sheet Form I want their information to automatically be generated into the form based on the information from the original table.

Hope this is clear. Thanks.

Travis

View 1 Replies View Related

Access 2000 To 2003

Aug 26, 2005

How unusual is it for a database to become MUCH smaller when converting it from 2000 to 2003 formats. I mean 600megs to 10 megs. And as far as I can tell all the records are intact....!

View 1 Replies View Related

Access 2000 Run Time

Mar 16, 2006

I have a rather large project that has been developed in Access 2000. Generally all my distributions have been to PC's with Access available. However, I do find that there are a few that don't. Obviously this is where Run time would be useful. Having never used it I have a few questions if someone could possibly help:-

1 If I have a program developed in Access 2k can it be converted to the MOD version to include runtime?
2 If there is a conversion process is it automatic or does it require extensive reworking of the project?
3 Is it still possible to purchase MOD 2000 or do I need to get an updated version and if so will that still work with Office 2k?

Thats about it. Thanks

View 2 Replies View Related

Access 2000 Problem, Can Anyone Help?

Mar 19, 2006

Access 2000

How do I copy the results of a query to a new table that serves as an archive?
How can I then clear or reset the original query?

I am doing a project that records the names of students who have had more than 5 merits in school. I want to use this data to generate a certificate to give out at assembly and to send a letter home.
I have run a count query but want to send the results to another table which acts as an archive. I want to have the original query reset.
Can anybody give me any suggestions?

Very much appreciated.

View 7 Replies View Related

MS Access 2000 Problems

Jun 14, 2006

We are a school operating around 160 computers with Microsoft Office 2000 fully installed on all of them. There have been problems with Microsoft Access in one particular room where students frequently lose their Microsoft Access Reports as a result of clicking on Print, which is then followed by the following message:

Msaccess.exe - Application Error : The instruction at "0x77fcd79a" referenced memory at "0x00000000". The memory could not be "written".

This problem occurs when the students try to print their MSAccess Reports.

The computers in the entire school all have Windows 2000, latest service packs, patches, hotfixes and service packs for Office 2000, but for a reason we can't identify this only happens in one room. It is connected to a QMS Magicolor 2350.

Can anyone help us with this problem?

View 1 Replies View Related

Access 2000 Db Conversion To SQL

Nov 10, 2006

Hi,

I've got a fairly complex access 2000 database that's split into front and back ends.

There's 32 back end tables and a whole host of complex front end queries and forms. The whole thing's around 25,000 lines of code so not a 5 minute job to rebuild! (sorry but i just can't stop tweaking it!!)

I hate the idea of all that data moving across the network, and having to make the front end machines work so hard so I'd like to get the server doing it's fair share of work because it's got dual 3Ghz Xeon processors which aren't earning their keep! I was thinking about converting the mdb to SQL and although i could pay someone to do it for me, i do like to learn! (so when it breaks i'm not at anyone's mercy!!)

I assume there isn't an upsizing tool on the planet that will let me do this by clicking a button so can anyone point me in the right direction with regard to something decent that can automate as much as possible. PLUS a good source of information on the differentces between access and SQL so that i can start seeing how to accomplish with SQL what i'd do in Access?

Thanks,
Vince

View 3 Replies View Related

Access 2000 To 2003

Jul 27, 2007

I have 5 databases running 100% in Access 2000. We are switching to Office 2003 within a couple weeks via an upgrade. What should I do to prep my databases for the switch?

Is there any upconverting tool I need to do ect? Thanks.

View 9 Replies View Related

Access 2000 And 2007

Apr 10, 2008

I am running Access 2000 on the network. Recently I upgraded one of the users to Access 2007. All is well, except she cannot get into one of the databases when other "2000" users are in it. When she was using Access 2000 this wasn't an issue. Is there a way around this?

View 1 Replies View Related

Difference Between Access 97 And 2000

Sep 4, 2006

Hi all, I don't really have a problem for anyone to solve but I do have a question as to a difference I found between Access 97 and 2000.

I am in the process of converting some 97 databases to 2000. I am doing this by simply using the convert option within Access 2000. I thought things would have been pretty straight forward, but the first database I converted didn't work the same way as it did in A97. The first difference I found is that I have a button on a form which has the following code.

Private Sub cmdSearchAM_Click()
DoCmd.ApplyFilter "qryAMSearchFilter"
Me.AMFilterInput = Null
End Sub

The query "qryAMSearchFilter" looks at the combobox AMFilterInput which is on my form. In A97 I could simply pick different names from the combobox and then each time I clicked my button it would filter the form correctly, however in A2000 this isn't the case and I have had to do the following.

Private Sub cmdSearchAM_Click()
Me.Requery
DoCmd.ApplyFilter "qryAMSearchFilter"
Me.AMFilterInput = Null
End Sub

Would somebody be able to explain to me why this is now happening and if there are any other things I should be aware of when converting to A2000 from A97.

If anybody thinks my solution to my initial problem is wrong could you let me know, even though it seems to work I don't know if it the appropriate fix.

Cheers,

Steven.

View 1 Replies View Related

Access 2000 Incompatibilities

Sep 24, 2004

I wrote a program in 2003 (using 2000 file format) and for some reason, I cannot get it to work on my client's Access (he uses 2000). The problem is that off of the switchboard, some forms will open and some will just not, and will not give any error message either. I'm thinking it may be because of subforms (I have 2 in the form that won't open) because none of the other forms have subforms, but then again, that doesn't make any sense why 2000 wouldn't be able to support subforms. Does anyone have any help before I lose this job? Thank you so much!

View 2 Replies View Related

Add A Record Using VB In Access 2000

Oct 13, 2004

I am getting a type mismatch error when I try to set up a recordset to add a record to. Here is the code:

Dim MyDb As Database
Dim MySet As Recordset
Set MyDb = CurrentDb
Set MySet = MyDb.OpenRecordset("GAME STATUS",dbOpenTable,dbAppendOnly)

I am getting the error at the Set MySet = MyDb.Openrecordset("GAME STATUS") statement. I needed to add some reference libraries to get the Database type to work as Access was just installed on this computer. Is there another library that I need to install to get the recordset definition to work or am I just doing something wrong? Thanks in advance for your help.

View 3 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

Covert MS Access 2000 ---> 97

Jun 21, 2005

Need to convert 2000 to 97 but have not got access to Access 2000.
Is there a program out there I can use?

Thanks
Norm

View 1 Replies View Related

How Do I Get/use MSCOMM In Access 2000

Mar 6, 2008

I am trying to use Access 2000 to create a database for an industrial application. I have successfully coded and created most things, however I need to access the serial port for both incoming and outgoing data. MSCOMM appears to be the tool that I want, but I am not sure if it is available. Can someone please point me in the direction of obtaining or loading this tool. thank you

View 3 Replies View Related

Comment Out SQL In Access 2000

Nov 13, 2006

Hello,

Really simple one this one. Though I can't find it in the help or in forums. So simple it doesn't rate a mention apparently.

Except that I would dearly love to know how to comment out SQL code whilst writing queries in Access 2000 SQL view. All that retyping and copy/pasting is causing me RSI.

In eager anticipation,

Tim

View 1 Replies View Related

MS 2000 Access Application Is Crashing

May 30, 2005

My MS 2000 Access application is crashing and returns a msg something like this: "Your Access application caused an error and Access must be closed"
This occurs only when I execute some forms; always the same forms cause the problem.
Somebody told me to copy the .mdb to another place in my HD and change the names of the forms.
I did it and it always works well!
Any good reason for that?
My app is not yet splited in FE and BE. Splitting would help?

Thanks a lot

View 1 Replies View Related







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