Hi there, we currently have a database that users write to through an excel form. Each time a user sumbits a record to the database we open a connection up with the below code, insert the record and then close it. This creates a ldb file for 1-3 seconds depending on how long it takes.
' OPEN DATABASE CONNECTION
Set dbConn = New ADODB.Connection
dbConn.CursorLocation = adUseClient
dbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0"
dbConn.Open sConn
' INSERT RECORD THROUGH EXECUTE COMMAND
dbConn.Execute("INSERT INTO.... VALUES....")
' CLOSE CONNECTION
dbConn.Close
Is it possible to open the connection up without an ldb file being created so more than 1 user can insert a record into the database at the same time?
What I'd like to do is have an "export button", where the OnClick event, exports a single table into a new accde file. This would allow the end users to zip this file up and send it to me.
Because of all sorts of stupidity, I have no network capability and must send data back and forth via email.
I have digressed to a replication table that needs to be uploaded, once data is entered by the users...
Because the files can get relatively large (for emailing purposes), I am trying to figure out how I can get just one table from them vs. sending the whole application file back to me... It's pretty vital that they not be able to edit the table because that could really mess up the process.
So any way to create a new lock file from within the current Db?
I am a novice Access user. The idea is to build a visit tracking database. We are currently using excel to input appointments and outlook for statistics and patient data (as contacts). I am basically doing double the work.
The excel sheet is one week (Mon-Sun), the dates are in the top row; the names of patients are in t/he left-most column. The visit is marked by the name of the therapist. So, if John Doe has a visit from Linda on 08/25/12, there would be a "L" in the cell corresponding to the date and patient. There are also initial visits by the head therapist and there is a letter "IV" in the cell to indicate that.
I know that I can import the contact list from outlook and create a table and I know there's a calendar function.
Is there a way to 1. Create a form that will act as a weekly calendar (with each cell in a column having the same date, indicated in the top row) with connection/sync with outlook? 2. Be able to customize this form: add and delete patients in the left column. 3. Have the form count number of visits (# of total visits, # of total visits after the initial visit). In other words keep statistics of various sorts.
I created a database for one company. So, this database needs to support multiple users environment. One of the users has problem opening the database. Sometime he can open it. …. sometime he has an error message “Could not lock file”. What’s wrong with the database? (I gave him permission to “Read”, “Write” and “Read & Execute”) Thanks in advance for all your answers.
Hi all, We are changing domains and are doing alot of bouncing around with passwords and such. Now all my users who are not in admins can not open our Db. We get a message 'Could not lock file'. Any ideas? thanks
Hi folks - need some help with what is probably very easy but I just can't get it.
I am running an access database on a 2 pc network with shared access etc. I can see all files on both computers but when I try to open the same file on both systems I get error message " could not lock file" I had another database earlier this year working on the two machines perfectly and was able to input data from both and the file was not a froint end back end structiure.
also if is any help the second machine only seems to be able to open in Read only mode. I have tried the user permission on the my computer allow shared access but this has not fixed it.
We have manufacturing/shopping software that has an access backend. There is about 15 users access it via terminal services.
From what i know, access only allows for 256 connections, and that number can be viewed with the LDB Viewer.
The problem is that if someone doesn't log out, the database doesn't reset itself. The next day, the lock file will still be a high number, and the shopping software will have errors running reports.
I dont understand how this happens. If there only 15 users, how cant there be so many connections? What makes the count build up? What can i do besides kick everyone out to reset it?
Hi, I have created a File DSN using my ODBC under Admin Tools and placed it on the network and have linked my Access 2003 tables to point at this.
I have asked a non-domain user to log into this database and open a table but when they do, it defaults to a Domain account until you untick trusted Source. You then have to enter the username and password for the ODBC connection. I thought the whole point of a File DSN was to eradicate the process of logging in. Can anyone Help?? Thanks
This is what the ODBC looks like through text editing software.:eek:
[ODBC] DRIVER=SQL Server UID=MyUserAccountName LANGUAGE=us_english DATABASE=MyDatabaseName WSID=MyWorkstationName APP=Microsoft Data Access Components SERVER=MyServerName Description=MyDescription
In the lock file I noticed I can see the computer names of the people who have the db open. Is there a way to get the login names from this file so I can knock some sense in my co-workers who still have the database on
I think the damage to my database is not recoverable, but i'm still hoping. My computer crashed when I was working on my dtabase and after restart the db is not working anymore :( If I try to do something, anything (!!!) all I got is an error message: "Error accessing file. Network connection may have been lost." But the db is on my computer, I don't use it over the network. What can I do to make it working again? Thanx!
I have a picture stored on a network file share that populates each time a particular form is opened. Every so often we lose our connection (which is a different issue altogether). Right now the error I get after much grinding away, is "Bad file name or number"
Is there a quicker way to check for the connection before it spends 45 seconds trying to find the whole path?
Code: Dim vFolderPath As String, dirFile As String, strFile As String vFolderPath = Nz(DLookup("FolderName", "tblCodes-FolderControl", "FolderKey = '" & "Profile" & "'")) dirFile = vFolderPath & Dir(vFolderPath & ctrl_people_id & " *", vbDirectory) strFile = dirFile & "profile_pic.*" 'Debug.Print dirFile On Error Resume Next If Dir(strFile) <> vbNullString Then Me.[ctrl_ImageFrame].Picture = dirFile & "" & Dir(strFile) Else Me!ctrl_ImageFrame.Picture = "X:~stuffprofile_icon.png" End If
I have some code which updates an invoice table with transaction numbers. It worked ok until this month when we had to run three months worth of invoices in one go. I recieved a runtime error 3052 "File Sharing Lock Count Exceeded" and Access told me to increase the MaxLocksPerFile registry entry. I thought i'd better check it out a bit first though and found the following on support.microsoft.com
In Microsoft Access 2000, you can take advantage of a feature that allows you to increase the MaxLocksPerFile setting for Microsoft Jet database engine 4.0 by editing the Windows Registry. The default value of this setting is 9500. However, changing this value is not recommended if one of the replicas is located on a Novell Netware server, because the server's maximum record locks per connection is 10000.
The invoice table had about 20k lines but as I am on a netware server i'm not gonna be able to squeeze more than 10k lines out of my recordset so I was wondering if there is any way I can open it without record locking? The table that is being modified is never edited apart from in this one process so there is no risk of complications there.
Here is how I opened the recordset
Set rstInvs = dbs.OpenRecordset("SELECT [InvNumber], [InvNumExt], [TransactionType], [LineNum] FROM [tblInvoices]" _ & "WHERE [LineNum] Is Null ORDER BY [InvNumber], [InvNumExt], [TransactionType]")
Trying to lock down an application by saving as ACCDE file. Saves OK but when you load application none of the controls work. I.e. button clicks do not launch "Open Form" or " Print Report" commands.But in ACCDB format application works flawlessly.
When I tried on a different machine I get an error message that says too many Table IDs. Odd since I only have 3 forms, 3 tables and 3 reports. Lots of DoCmd's and switching between the 3 forms but not sure why this would create "too many Table IDs"?
We have a split ACC2010 database, with the back end on our server and 5 front ends on 5 client PCs. I hadn't run a compact/repair on the back end for months. When today I tried, I got the 'database is locked' error. The lock file showed 'Admin' logged on at all 5 clients. The front-ends had been closed on all 5 clients. We do not have a user called 'Admin'. Because my issue occurs for all clients, it might be caused by some sort of programming error in the front end.A VBA bug (implicit comparison to a control in an If .. Then construct) caused this type of behaviour but was resolved in Access 2007.
I have been developing Access 2K applications. Lately, every time I click a command button on a form or even open the code window for a form, I got this repetitive error message. I even create BLANK Access db and re-import all objects or create brand new form and copy the control designs/code from the error one to the new one. The new db or the new form still have the same ERROR, which is as follow :
"The expression On Click you entered as the event property setting produced the following error: Error accessing file. Network connection may have been lost. *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluation the function, event, or macro."
Please help! Thanks so much for your suggestions/ Have a great weekend,
My company has a network with an Access application front end and an Oracle back end. The back end is read only. The folder in which the Access application resides is shared with other departments who have been concerned about people who use our application having any contact with their applications, whatever they may be. Everyone who used the Access application has had Full Control rights on the old folder.
Our application was moved to another folder which is in another folder to which no one has access. To get to it, the complete path must be typed in; the browser can't be used because the forbidden folder can't be opened.
Only a few users need Write access to the application. Everyone else has been given Read Only access to the folder in which the application resides.
We are now finding that when the second person tries to access the application in its new location (it was deleted from the old location), they get the "Could Not Lock File" error message and the applicattion won't open. The Access application has no security on it at all-everyone has full rights to do anything they want in it. At corporate's insistance, the only controls on who can do what are the permissions that are associated with the folder.
We are having a few of them apply for Read Write access to the folder to see if this solves the problem but it will be a few days before anyone gets the permissions changed.
In the meantime, does anyone have any idea if this is the problme or could it be something else? By the way, this only started happening in the last day or two but people have been requesting permission to the new location for about a month.
I use office 2003, first I had to convert my database, then I did the database splitting then, remove all menus from running at startup, I tried to create an mde file but it didn’t work.
This is what I got:
This error is usually associated with compiling a large database into an MDE file. Due to the method used to compile the database, a considerable number of TableID references are created for each table. The Microsoft Jet database engine version 4.0 can only create a maximum of 2048 open TableIDs at one time. Exporting a database as an MDE potentially can exceed this limit if the database has a large number of objects (table, macro, form, report, etc).
There is no accurate method to estimate the number of TableIDs the Jet database engine uses during the process of compiling a database as an MDE. However, each VBA module and each form uses one TableID, as a result, if the database has 500 forms, and each form's HasModule property is set to Yes, as many as 1,000 TableIDs are used.
So installed office XP and tried the same steps but still not working.
I am running access 2000 9.0.2720. I wanted to create an MDE file but every time I attempt I get the message 'unable to create an MDE file'. Any advice?
I am just having a heck of a time, in trying to make a custom help file.
Has anyone had any luck? I sure would like to see a sample database, showing a custom help file. I just about tried everything, but not to smart about setting one up. I have tried the wizzard as well. I know where I get hung up is, trying to map the help file, to the Help ID number.
I was able to create a MDE database from .MDB file (1900 KB) using Access2000 but not on the other database (5000 KB) I followed the steps (under tool -database utilities- make MDE file), the message box shown " Microsoft Access was unable to create a MDE database"
Is there any limit on the size of the file? What should i do or check?
I have tried to create an MDE file for my database but can't figure out why it will not let me. I can't don't think I am over the limit in open TableIDs. I have 76 Forms and 91 Tables.
I am setup with two databases - the database with the data is on the server. Is there any other alternative that I can do other than putting the mdb file directly on the client. And if not is there a way to stop the mdb file from constantly growing in size.
Hello, I would like to know how to create an empty Excel file (from Access) ? I would like to name the workbook (xxx.xls) name the sheets and give the folder where to store it. Thanks in advance for help. VINCENT
I created a help file in access that lets you have text mesasge up to 1024 chars. Just press F1 key on any control that you have setup to bring up help message.
Does any one see any problems with the way I did this?
Form Help File: = "" Help Context ID: = 0
For each control on the form where you would like to have a help message do the following. Form Control Help Context ID: = Set to one of the help index numbers in the help table.
Add KeyDown Event to the control
Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer) 'Add this line KeyCode = DisplayHelp(KeyCode, Me.ActiveControl.HelpContextId) End Sub
module
Function DisplayHelp(KeyCode As Integer, HelpContextId As Integer) As Integer Dim dbs As DAO.Database Dim rstRecords As DAO.Recordset
If KeyCode = VBKeyF1 Then ' 112 = F1 Set dbs = CurrentDb Set rstRecords = dbs.OpenRecordset("SELECT tlbHelp.Index, tlbHelp.HelpMessage " & _ "FROM tlbHelp WHERE (((tlbHelp.Index)=" & HelpContextId & "));")
With rstRecords If .RecordCount > 0 Then Call MsgBox(!HelpMessage, vbInformation, "Help") End If End With DisplayHelp = 0 ' Clear KeyCode rstRecords.Close Set dbs = Nothing Else DisplayHelp = KeyCode End If End Function
Create Table and Fields
Table Name: tlbHelp Field Name: Index as Number Field Name: HelpMessage as Memo Field Name: FieldName as Text Field Name: FormName as Text
How type in custom number and message for each help message.
