I have been posting a lot recently and have greatly appreciated the help. I have just been looking at the size of a ms access project, this is a front end for another database so contains no data (runs as an mde for most). I have made a few modifications using access 2007 including at one point having it in the new file format, this was to fix a bug I was getting that caused a particular crash. I found converting it to 2007 and back cleared the problem.
Anyway the main things I added were an active directory query to enable certain buttons for a limited number of users. Including the VBA reference to do this. I also added a new report that when run in access 2007 (even in 2003 format) allows the new reportview so I can have an onclick event within the report for the user to gather more information which it wouldn't be appropriate to have on the main report.
This has balooned the size from 5mb to 50mb even after a compact and repair. Any suggestions as to possible causes could it really be adding vba references causing that much of an increase. I should say i'm not particularly worried it's ok for it to be that size I just wonder why such a massive difference.
I've just successfully spitted a DB of 30+ MB of size to front end and back end. However the size of front end is roughly the original one and the size of back end is some KBs. Shouldn't have been the exact opposite considering that data are stored to the back end?
I have imported a list from Excel into my database. The primary key is long integer (Indexed no duplicates). After the (successful) import, the next primary key assigned to a new record has jumped from (for example) 15 to 30024455. Why would this be? I have compacted and repaired the database (Access 2007) in case it was some kind of corruption but with no effect. Any primary keys on tables where I have not imported data works as expected i.e. incremented by one.
Hello I'm looking for a web based front end to put on my access database. Due to the way our work site is set up and the people who will be entering some of the data my boss wants it to be accessed through a web browser. Infopath wont let me enter data into an access database through a web browser and with DAPs becoming a thing of the past we dont really want to use them either. Some of the forms will be used for raw data collection while others will be used to fill out spill reports (i work in an environmental department) so we can get the information quickly. We have tried just using access to run everthing but have meet alot of resistance as most of the people who enter the data hate it. I also had someone enter half a spill report into an SQL sequence once..dont even know how he got there. That is the main reason we are looking for a web based data entry system. I dont believe my boss wants to use anything but access for the storage. So basicly we are just looking for a way for people to use a web browser to enter data into an access database. Any advice would be greatly appreciated. Thanks
i am creating a database that will house some supplier information. once designed, the db will be populated with all the data and then it will be used as a reference tool for future projects.
it was requested that an "excel front end" be used for lookup and ease of use to access the data when it is finished... i am not really sure how that works.
also, what is the best way to create contingent variables in access-- for example, if a certain value is in the database then you have a corresponding action. like if there were a field asking how many suppliers there were, and if say the number is 4, then 4 entry windows come up. a sort of if/then statement?
I split my database so that users could only edit/view data through the forms but when I opened the front end the tables where still there (linked though) and I could edit and look at them. I assume this is normal so how can I prevent users from seeing the tables. Do I just set them to hidden? Thanks.
I am a computer novice with no experience in PHP or SQL Servers in general. Is there anyway to set up Access as a front-end to an MS SQL Server without having coding knowledge? Is there any good wizard that will export data/formats to a SQL Server while still keeping the interface mechanisms of Access?
I have an Access front end that has linked tables to a SQL Server back end database. The connection to the back end is a via a read only user because the front end is a reporting tool for querying, viewing and reporting on the data. I am able to view the data in the front end and set up new queries. However my user has the new query icon greyed out. I have not set up any kind of permissions or security levels to my knowledge so am a little mystified as to why their permissions should be different to mine! Does anyone have any ideas please?
We upgraded to ms access 2003. We used to be able to make design changes on just about anything in access 97 IF a user was not in the particular record/form/object. Now it seems we can't do much of anything in access 2003 unless in exclusive mode. (and we have many many users 24 hours a day) so it's even hard to import changes made off-line.
Is there anyway around this in 2003? Any other suggestions? Other than calling all users on the phone to tell them to log off (and some are logged in and not even there), can we 'boot' everyone off (although this really is not a good option for us either). This is a production database.
It would be good if we could just make quick changes as needed when we want as it was in the version 97.
I created a database in Microsoft Access and then upsized it to SQL Server afterwards.
I now need to add a new column to a table in the database. So I added the new column in SQL Server using ALTER TABLE command. This column is a foreign key which has an associated parent key in another table which contains a description of the column. e.g. Main_Table Case_ID Foreign_Key_Field_ID 1234 78
New_Foreign_Key_Table Foreign_Key_Field_ID Foreign_Key_Field_Desc 78 Describes the ID 78
I also setup a CONSTRAINTREFERENCE between the fields in these 2 tables.
I then relinked my tables using the Linked Table Manager to pick up the change to the table I altered (Main_Table). I also linked to the new table I created (New_Foreign_Key_Table).
My problem: In Access if I create a new table I can select the lookup value for a field in the Table Design view Lookup tab. However, I altered my table in SQL Server and then linked to it again to pick up the change. Now when I view the linked table in Access it does not have any lookup value AND it's not possible to change this for a linked table.
Is there some piece of SQL I can use in SQL Server to change the lookup value for the field in my table OR have I approached this the wrong way. Should I have modified the local table in Access and upsized it again?
Otherwise if I select all values from my Main_Table in a form it displays the Foreign_Key_Field_ID instead of the Foreign_Key_Field_Desc because the lookup value has not been set to Foreign_Key_Field_Desc
This posting is a bit long - I hope it makes sense.
Any ideas or suggestions would be greatly appreciated.
I have an MS Access front-end with a SQL back-end database. It works great, I connect the MS Access front-end to the SQL back-end using a File DSN.
I have a few users (who are in the database the most) who get ODBC disconnection errors time to time. The cause of the error is not consistent, they seem to just appear out of no where. The table that reports the disconnection is not consistent either.
The only way for the users to resolve the errors is to exit the MS Access database, and then re-enter it.
I have had the DBA team analyze the SQL server, and it does not appear to be having any difficulties. We moved the database to another server (so it would be on a server with less traffic) and I rebuilt the File DSN. But the errors continue.
I will have the network team look into any errors, but I am not certain if they will find anything or not.
Has anyone run into this, and what resolutions did you come up with/work arounds did you perform? I am starting to consider upgrading to a Visual Basic .Net front-end, but wanted to see what options were left.
When converting the Front End of my database to an MDE, Access just hangs and says (Not Responding). I've left it our an hour and there's no sign of stirring.The Back End converts in just a second or so.
I was successfully using a front end and back end setup for the past two years. Today I needed to push through some changes and "deactivate" the previous back end database and setup a new back end database. The new back end database has been sent; however the front end is not recognizing the back end database anymore. I need to re-link the existing front end to the back end database - I cannot import the front end into a "fresh" file because I put a password on the VBA Editor.
So I have finished a database and split it which is great. Only issue now is how do I turn the front end into an executable so no access to any parts of the access program or system can happen? I have done this before in a very old version of access but how to do it in Access 2013.
How to secure SQL data when my MS Access database must access it using linked tables. Generally speaking on the Internet, assertions that migrating to SQL is more "secure" are rampant; explanations of just how that is are scarce.
Sure I can restrict access and put different roles on different tables, then put different people in those roles, but that doesn't really do me any good. Ultimately I want to achieve the following: Allow them to do all kinds of stuff (including Update, Delete, Insert) using my access application, but I would NOT want them to have those same rights if they installed SQL Server Management Studio, or for that matter, simply created an ODBC connection using another access or excel application.
In other words, ideally, I'd like to have SQL server view my Access application as a user in its own right. Not sure if that type of impersonation or identity is possible to achieve.
Will keeping your field size shorter result in a smaller MDB file?
Or does Access only use as much space as there is real data in its fields.
Way back in the dBASE III days, dBASE would pad all your "real" information with as many spaces as necessary to fill up your field. I suspect that the MDB structure is probably smarter than that.
Another question on the same topic - I believe there is a maximum number of characters in a record (4000?). Can your field sizes add up to more than 4000, as long as the actual data, all combined, never totals 4000...? Thanks............ ..dc