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?
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...?
Recently Access has started to suddenly close. Usually when I have been developing although I do not appreas to have lost anything as I Save, Save, Save all the time. Last week I had a report that a live access 2000 is doing the same thing. Users are in the middle of entering data and suddenly the database is gone, with no messages. They can re-open the database and continue.
Anyone else experiencing this problem with Access 2000?
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
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.
My Acess2000 database holds records of members of an organization which I manage. I've got a button on my most-used form, called 'local members and helpfulness', which opens a report designed to print one envelope, using address data from the current member record showing in the form. I did this by configuring the button execute a macro. The button's 'On click' event is set to the macro name: 'mcrPrintC5env':
It works fine; the report shows up in preview mode so that I can click 'print' to print off that single addressed envelope. However, if I put an identical button on another form which uses the same data from the same "local members list" table (via a different query), it doesn't work. When I click the button in form view, a popup input box appears.
Need to know if I can open a MS Access 2000 (large) mdb-file in MS Access 2003 Professional without loosing any functionality? Is there a working way around this behaviour of MS Access 2003? It seems now, when comparing from MS 2000 openings some functionality got loose when I open the same 2000 file in Access 2003. Working environment is MS XP professional and all security patches for Office 2003 Pro and XP are applied.
I have got an existing query which is exported to excel through DoCmd.Transferspreadsheet. In simple terms the query looks like below:
Group | Client
Group1 Client1 Group1 Client2 Group1 Client3
Group2 Client4 Group2 Client5 Group2 Client6
etc.
As some of the groups contain large number of clients, I am trying to find the code to split groups by pre-specified number of clients and export to excel, as follows:
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
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?
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.
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?
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.
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....!
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?
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?
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.
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?
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.
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?
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.
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!
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.
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?
