Modules & VBA :: Users With Different Mapped Network Drives
May 13, 2015
I need to make sure that all users on the network can access a specific network drive folder.
Most users don't have a problem but some users have the network drive mapped differently so when running the feature required their system can't find the file at that specified location.
I have the location of the files hard wired into the VBA code.
E.g. Z:INVDATACOMPANIESCompanyAASSETS Pty LtdPrecedents 2015STAGE 3A - COLLS-DEMS
The location is outside of the project address.
I need to be able to make sure the address of the folder is the same for all users.
I am attempting to link a table in one database to another database using the server name designation (ie.//2sxfprinff01Group) instead of the mapped network name (ie. G:). I tried relinking using the Access Link Manager. Right click on the linked table. But it does not let you edit the link.
Access 2007 does not allow this and reverts links to the G: drive. There are situations where the the IT department have set the mappings to this shared location as an H: for some of the employees. In those cases, the employee gets an error message telling them that Access cannot locate the G: drive.
An obvious solution is to get the employees remapped to the G: drive. However, one would think that MS would have allowed for mapping to the designated location and not an alias.
My database is used by a few users on a shared drive in "read-only" mode so they can't change any data.I set a scheduled task to copy an updated version of the database (which I edit) every morning.
The problem is that when the users forget to close the database file I can't overwrite it.For this purpose I wrote a small code that quits the application at 00:00 using Application.Quit.The code WORKS when I test it on my computer, but every morning when I try to open the file I see that it is opened by another user since I also open it as "read-only" - meaning the code didn't work...getting the code to work properly OR get a better solution to be able to overwrite the file even though it's opened by other users.
I have designed a database which I intend to split for multi-users to access from one front end icon on a shared folder at work. I have designed a form bound to cmr record table and on it a subform to another table where cmrs activities will be saved. The form has buttons to and blank controls.
User can create a new activity entry by typing into the blank controls and pressing the save button which saves to the activity table. the edit button extracts a selected records details (selected on the subform) from the activity table and populates the blank field for a user to edit and then hit save to save changes. The delete button deletes a selected record from the activity table. I intend to have multi-users either accessing, viewing and a possibly editing the same customer at the same time. The simultaneous viewing is essential but the simultaneous editing, though not desired is inevitably going to occur.
What I would like to know is:
1. Can you lock an individual record in a table or does the whole table have to be locked. E.g If Colleague 1 is editing Cmr A's record in Table1 can he lock it so Colleague 2 can view and edit Cmr B's record in Table1
2.Can Colleague 1 access/read Cmr A's record in Table1 to retrieve details toe the form controls if Colleague 2 is viewing or editing Cmr A's record in Table1
3. If record lock is possible, how can I initiate it in my example code below.Edit activity record Code
Code: Private Sub Edit_A_Click() 'Get Data to text box control With Me.R_P_Data_P_Subfrm.Form Me.txtrID = !rID Me.txtrID.Tag = !rID Me.txtrefNo = !refNo Me.cmbrpc = !rPC
I have a module for importing several excel files from a folder in Access. The user is prompted to select the folder which contains the Excel files.The issue:The code works fine if I select a folder from my company network drive (strFile variable returns with correct file name).The code does not work if I copy and paste the same folder to my desktop and select this location (strFile variable returns empty)
Code:
Option Compare Database Private Sub btn_ImportData_Click() Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean Dim intWorkbookCounter As Integer Dim lngCount As Long Dim objExcel As Object, objWorkbook As Object Dim colWorksheets As Collection
I was just wondering how many users Access would be able to handle on a split frontend and backend database? The backend will be on a network drive and the frontend will be installed on each workstation. My database is pretty simple with no relationships if that matters. Also using Win7 and some users will have Office 2010 and others will have 2013.
So I do map drive on this way and it is successful ...
Code: Dim FSO As Object Dim objFile As Object Dim objFileS As Object Dim WshNet As Object persistent = false set objNetwork = CreateObject("WScript.Network")
But I need to have mapped drive hidden from the client users So I red that i might be possible if put $ after the share folder
Code: objNetwork.MapNetworkDrive "driveletter:", "serversharename$", persistent, "username", "password " but it does not work.. the other way is to set values in win registry, but I don't like that way...
I have been searching and found a few great threads about checking users but the codes there were incomplete. Is there a way to effectively log a user's name & computer name when they open your database and also log when they close/exit your database. Without them noticing but just for your own personal security incase something happens and you would like to know who all was actually working in/on the database at that specific time?
Or just something that will show me who is in the database so I dont' have to keep seeing network333 something has it open.
Thanking you all in advance, and I know I am a P.I.T.A.!
I will have around 10 simultaneous users of a database. Each will probably keep it open all day and subject it to fairly light use.
I will split it into front and back ends, the be going into a network folder.
Where would you recommend I put the fe?
Should I put one fe in the same folder and let them all use it at the same time? I presume that would be faster than giving them all a copy of the front end to use on their local hard drives?
I have considered adding the facility on the fe to allow user-definable defaults, like the user's name, to speed things up for them but i assume that will only work if they each have their own fe and not sure if it's worth the hassle if it's faster to have one fe in the same network folder as the back end.
Alright, so my pride and joy and departmental savior database has been fully implemented and people are so pleased with it that they *all* want to use it at the same time. Initially, I made it custom for three peoples' UI preferences, but since it's such an intuitive design, other staff now feel comfortable using it instead of passing the work down the line to the one person who knows how to use that "newfangled thing".
A couple years ago, I used FMPro and allowing for multiple users at once was an annoyingly complicated process dealing with permissions and rights and accounts and passwords and yadda yadda. Is it any easier to set up in Access? Does anything special even need to be done to allow for multiple simultaneous users?
My workaround was to temporarily map the URL to a vacant drive letter on the local machine, then copy the file over, then drop the mapped drive again. A bit clunky but doesn't incorporate much of a delay and this download only has to happen once per day.
The good news is, the file download / copy now works every time. The bad news is that removing the temporarily mapped drive after the copy has taken place, doesn't and I can't figure out why.
Here are the functions I use to map / unmap the drive :
Code:
Option Compare Database Option Explicit
Private Const RESOURCETYPE_ANY = &H0& Private Const CONNECT_UPDATE_PROFILE = &H1& Private Const RESOURCE_CONNECTED As Long = &H1&
[Code] .....
I have separate functions to check existing drive mappings on the local machine and thus determine an appropriate vacant letter to use for the temporary mapping - they work fine.
Unfortunately the UnMapDrive function returns False (even though I switch the active drive to C: and force the connection to be cancelled with the fForce flag) So the mapping always remains on the users profile.
I don't want to permanently map drives on the users' profiles, just briefly for the purposes of this daily file download.
So I have a table containing 2 fields containing the first name and last name of a customer. I do need to add these names in the same table into a new field called Name. Can someone help me out?
First, I'm developing both the Front End and Back End in Access 2007, and I intend to distribute the application using the Access Run Time. The powers that be will not let me use SQL Server.
The database is required to house attachments. Initially, I had created a separate back end database to store the attachments, but the file would quickly grow beyond the 2GB limit - roughly within 3 weeks. Instead, I decided to save the "attachments" in folders (named after the parent records PK ID) on the network. Using VBA, I would populate a list, based on the parent record, from which the user could select the file, do what needs to be done, then save it.
Unforunately, we're running into data security issues - all of the attachments will have private personal information - SSNs, Loan Numbers, Account Numbers etc., so they need to be stored in a location that's not universally accessible. All of the users have different permissions based on their department, acting as a very basic security level (i.e., Department 1 users will not be able to view any parent records associted with departments 2 or 3).
So I need a way for my back end to take a file located in a separate directory not normally available to the Front End User, then move that file (or save a copy that will be overwritten when re-attached to the record) to a location that IS accessible to my Front End User.
Can I create/use modules stored on the back end?
My first thought is to let the back end store the record in a temporary attachment table that is deleted when the user is done with it, but I've noticed that Access has trouble reclaiming the space when the attachment is ultimately deleted once the user is done using it, and I'll run into my 2GB limit again.
Alternatively, I could create a new .accdb file each time the record's attachments need to be accessed, and then delete that database once it's done and the attachments are returned to the secure location, solving my limit problems.
I have a button that adds pictures/files to a network location. So people from all over the company can do this and all pictures/files are stored in one location.
I also have a delete button, but a waning pops up and the file remains on the network.
PHP Code:
Run-time error '424': object required
Code: My.Computer.FileSystem.DeleteFile (Path)
where Path is a string, files location on network.
I have my database connected to a network of ten computers. I recently switched my linked tables from a Mapped Network Drive to using the actual UNC path \computerfolderdatabase.mdb
My users have started to notice it takes longer for the database to connect in order to log in. Once they have established the connection, the data transfer speed seems to be normal.
Is this to be expected? Any suggestions to speed things up for login? Or is this the downside to using UNC?
As the thread title suggests, my problem is that I have a database that will not open via the mapped drive. There are other databases stored on this drive that open fine so it is only the one database that is causing me a headache.
Any suggestions as to why only this one database would be having this problem and any ideas on how to resolve it would be greatly appricated.
I have a front end and a back end access files.My user save the back end on a shared network folder like:a.b.comfolderName$..I wish to simulate this path in my local pc in order to set the linked table links.My problem is that I can not modify my pc name to be "a.b.com".How can I simulate this in my windows 7 pc ..so I can set the linked table values to "a.b.comfolderName$" ?
I have a front-end and back-end database. Front end is at //fserv/database back end is at //fserv/database/administration
I have sent front end path to users and they can bookmark it to find front-end. When I linked to the backend in the administration folder I had the path mapped to drive T:
Problem is the users do not have this same mapping to drive T: They may have other paths already mapped to that drive.
The users are getting the message:"t: est.mdb is not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides."
Is there anyway to link the the backend tables using just a path like: //fserv/database/administration
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've inherited a few Access DB monstrosities. One of which had ODBC credentials hard coded in the MDE which I had to recently update due to some server updates. I found an unsplit version of the Access DB on a development drive (j:db...) and updated the credentials hard coded in VB and updated the linked tables from the J drive path (dev) to the K drive path (prod). I then split the DB and the MDE worked fine for me. I quickly found out that it only works fine because I had J mapped still. End users do not have the J drive mapped (development) only the K (production). Without the J drive mapped to the actual dev path the Access DB simply doesn't load. No errors.
I then tried to find any references to J in VB and fix those. At this point I can't seem to find anything else that could be referencing the J drive but still the DB will not open without J mapped.
Small piece of code that can send / recieve small text-strings over a network. I finally got it to work, but forgot to think ahead . Right now it only works in a formular but i really need to be a function with input / output.
My problem is that i am using the "Withevents" to call the Ostrosoft Winsock network module and "Withevents" does not work in functions.
How to build this into a function ?
Code: Option Compare Database Option Explicit Dim sBuffer As String Dim spage As String Dim WithEvents wsTCP As OSWINSCK.Winsock
After several days of searching, I haven't been able to find any threads related to this. I'm making a search form that queries a pdf library table. Once a search query is entered, the user is able to open the files from a results form. Currently, I am entering the filenames from the network directory manually into the pdf library table.
For example, I manually enter the following information into the tblPDFLibrary table that contains the fields:
Date added, Filename, File Path, Series, Class, Title.
I was wondering if there's a way to have Access query all the filenames in the directory for me. In other words, if the directory contains the filenames: AccountsPDF, InventoryPDF, CustomerPDF. Can I have Access query all the filenames within the directory and automatically add all the file names (AccountsPDF, InventoryPDF, CustomerPDF) to the Filename field within the tblPDFLibrary table? If I am able to do this, I can code the other fields to populate information because the filename contains all the other field information (except for file path but I can program it insert the UNC path).
I have a database that runs a macro/report every 2 hours, however our network has been up and down lately. This causes the macro to fail when trying to log into the system because the table that is storing your username is no longer in the database. When I check, all tables, linked tables, forms, reports, macros and modules are all gone. I find the only way to get them back is to close the database and reopen.
What I want is a way to reconnect the objects without closing and reopening.
I have two tables. The first is a list of users. The second is a list of projects. I need to loop through the user list and assign each one to a project. At the end of the user list it will just start over at the top until all the projects have someone assigned to it. What is the best way to do this?
Table1 Users
User1 User2 User3
Assignments
ProjectAssigned To Project1 Project2 Project3 Project4 Project5 Project6
After processing.
ProjectAssigned To Project1User1 Project2User2 Project3User3 Project4User1 Project5User2 Project6User3
I have a multi-user database. I would like based on the logged-in user, some buttons and controls in different forms be disabled and the others be enabled.
There is a login form that gets the username; I made also a function to define authorities for users in it.
My plan is that when user enters to the database, on load of the main menu the function calls and disables the defined controls in different forms. I tried to use this kind of codes:
Function User_1() [Forms]![frmMainMenu].[cmdUpdateDatabase].Enabled = False [Forms]![frmChooseReports]![cmdOrdersFollowUpReports].Enabled = False End Function
Function User_2() [Forms]![frmMainMenu].[cmdChooseCharts].Enabled = False End Function
But this code only works for the forms that are opened at the moment and if one of them is closed the system gives Error.
