Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    4

    Unanswered: Change Linked Table File Path For Each New User

    Hello Everyone! I am fairly new to Access and have very little vba experience. What I am doing is making a database tool that I would like to roll out to other employees (they would have local instances kept on their hard drive). It tracks an individual's inventory of spare parts with input of each transaction (i.e. order, return, itemization, etc.). I have been using it myself and it works great. I use linked tables to import data from another database. The file path of the database I link to includes an employee number (e.g. C:\Data\"Employee Number"_"xxx".mdb). Every employee's file path would be the same, with the exception of their employee number. I also use the employee number within this tool as a query condition, but I have only hardcoded my own employee number in place. My question is, how would I go about prompting the end user for their employee number the very first time they use this tool to establish their employee number? Would I need to create a variable that is null until the employee number is given? How do I use this variable later in my query condition?

    Thanks!
    Last edited by uwgreer; 04-06-12 at 14:18.

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Am not 100% on how DB is organised. However from what I can gather, one potential solutions could be this.

    Create a table (yourtablename) with one field (yourfieldname) that will store the EmployeeID, there should be a copy of this table on each users copy of the DB (i.e. not a linked table as each user will need their own unique copy of this table).

    Create a query (yourqueryname), you don't need to add any fields to the query as this will be done from the code. This query will ensure that only ONE EmployeeID is ever stored in the system.

    Create a welcome form that would run on startup that has the following code in the form module.

    Code:
    Public Sub PromptEmpID()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim myEmployeeID As String
    
    myEmployeeID = InputBox("Enter your EmployeeID", "Employee Identification Number", "XXXXXX")
    MsgBox (myEmployeeID)
    If myEmployeeID = Null Then
    'Do something if they don't enter a value
    Else
    'Write the value provided to the table
    Set dbs = CurrentDb()
    Set rst = dbs.TableDefs("yourtablename").OpenRecordset
    With rst
    .AddNew
    !yourfieldname = myEmployeeID
    .Update
    End With
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    End If
    End Sub
    
    Public Sub RemoveEmpID()
    
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("yourqueryname")
    
    strSQL = "DELETE EmployeeID.* " & vbCrLf & _
    "FROM EmployeeID;"
    
    qdf.SQL = strSQL
    DoCmd.OpenQuery "yourqueryname"
    
    Set db = Nothing
    Set qdf = Nothing
    
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    Dim lngIDCount As Long
    
    'Count records in EmployeeID table
    lngIDCount = DCount("yourfieldname", "yourtablename")
    MsgBox (lngIDCount)
    
    'If no value exists in EmployeeID table i.e. record count is 0
    'Then prompt for user entry
    
    Select Case lngIDCount
    
    Case 0
    'There is no value in the table so prompt the user
    Call PromptEmpID
    Case 1
    'There is ONE value already stored in the table so proceed as normal
    Case Else
    'There is more than ONE value in the table so remove all values and prompt user for entry
    MsgBox ("There is more than one EmployeeID stored in the system. You will need to enter your ID again.")
    Call RemoveEmpID
    Call PromptEmpID
    End Select
    
    End Sub
    You also need to ensure that you have Microsoft DAO 3.6 Object Library selected as a reference in VBA. To do this Tools -> Macro -> Visual Basic Editor -> Tools -> References

    The advantage of using this method is that the EmployeeID is stored in a table which means that you can pull this value at any time you wish to be used as a parameter in a query etc
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Hey Dave,

    I haven't had a chance to implement your suggestion, but I believe it will work for setting the local employee number. Now I am still unsure about how to modify the linked table file path to reflect the new employee number...

    Thanks!

  4. #4
    Join Date
    Jan 2012
    Posts
    97
    First perform the check as in the above code to ensure that there is no more than ONE record in the table. The code below assumes that the file/s are stored in the same folder as the master if not then remove the lines:

    Dim fPath As String
    fPath = CurrentProject.Path
    FullPath = fPath & "\Employee Number_" & EmpID & ".mdb"

    and replace with your file path, example below...

    FullPath = "C:\Data\Employee Number_" & EmpID & ".mdb"

    Code:
    Dim EmpID As String
    Dim fPath As String
    Dim FullPath As String
    EmpID = CurrentDb.OpenRecordset("SELECT (tblName.FieldName)  FROM tblName")(0)
    fPath = CurrentProject.Path
    FullPath = fPath & "\Employee Number_" & EmpID & ".mdb"
    MsgBox (FullPath)
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •