Connection and QueryDefs

[dccjr]

I am officially in over my head. I have the normal FE - BE setup (ie, the BE sits on a server, the FE on each user terminal). I am trying to connect to the BE using ADO, create and execut a dynamic QueryDef to populate a combo box. I think I have all the parts, but can't put it all together. This is what I have. This will probably not be an easy fix, but any help would be greatly appreciated.

CODE:

Private Sub cboFieldID_Change()
Dim cnn As ADODB.Connection
Dim strConnection As String
Dim strSQL As String
Dim strDBPath As String
Dim rst As ADODB.Recordset
Dim RstCount As Integer
Dim strResultType As String
qdfPopFields As QueryDef
' Open Connection
strDBPath = "X:\Operations\Manufacturing\Root Cause Analysis\RCAdbBE1.accdb"
strConnection = "Provider=Microsoft.JET.OLEDB.4.0;" & "Data Source = " & strDBPath
Set cnn = New ADODB.Connection
cnn.Open strConnection
'cboFieldID = ""
Set rst = New ADODB.Recordset

'Create New QueryDef
Set qdf = RCAdbBE1.CreateQueryDef("")

'Queries to populate IDNo combobox
Select Case Me.cboFieldID.Value
Select Case Me.cboFieldID.Value


Case "Work Order #"
strSQL = "SELECT RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[WorkOrderNo] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
Case "Quality #"
strSQL = "SELECT RCAData.[QualityNo] FROM RCAData ORDER BY RCAData.[QualityNo] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
Case "Part #"
strSQL = "SELECT RCAData.[PartNo], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[PartNo] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
Case "Status"
strSQL = "SELECT RCAData.[Status], RCAData.[WorkOrderNo] From RCAData ORDER BY RCAData.[DefectDate] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
Case "Area/Cell"
strSQL = "SELECT RCAData.[AreaCell], RCAData.[WorkOrderNo] From RCAData ORDER BY RCAData.[DefectDate] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
Case "Status"
strSQL = "SELECT DISTINCT RCAData.[Status], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[DefectDate] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
Case "Area/Cell"
strSQL = "SELECT DISTINCT RCAData.[AreaCell], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[DefectDate] ASC;"
Set qdf = RCAdbBE1.CreateQueryDef("", strSQL)
GetrstTemp
End Selectcnn.Close
Set cnn = Nothing
Set rst = Nothing
End Sub


Help me Please.

[June7]

Why are you connecting to the be with code? Why not set up links?

Here is how I manage multi-user split db: http://forums.aspfree.com/microsoft-...ue-323364.html

[dccjr]

I have edited the code in my original post to what I am currently working with.
Thanks. I went to the link. I would like to use UNC, however, I don't know any information about the server. Believe it or not, I don't even work in IT. I am a CNC machinist, who (stupidly?) suggested a new way to track the information. The project was dropped in my lap. IT sec is a little odd here (we are a defense contractor), so is there a was to get the information for the server locally? All I know it by is "x:".

[June7]

I can't say your network will be the same, but when I open Windows Explorer to MyComputer I can see the server name in the drive mapping. Examples:

CRM on 'DotAtuFs02' (R:)

Topo! on 'dotatufs02\Crm\Spatial' (T:)

Airport Photography and Graphics on 'Hyperion\lib\Aviation Masters' (V:)

2 server names are dotatufs02 and Hyperion.

Apparently, Windows is not upper/lower case sensitive.

I am lab tech in a construction materials lab who was tasked with building database for tracking lab test data. My only advantages were a couple of classes in computer science, an aptitude and affinity for programming, a very supportive boss, and a long work history dependent on the computer as a tool.

[dccjr]

Cool! Got that done and your sub in place. My next question is how do I use it in connecting to the BE? None of the books I have on Access, VBA, or VB (I have several) give any of the information that one really needs. Plenty of fluff (how to create tables, queries, subs, etc) but nothing of use. Can you help with implementing this in my existing code. I don't think that I will be able to update the FE. I don't have administrator right to, unless it would work the some as the user just saving a file? Suggestions?

[ssanfu]

Maybe the site will help you get the UNC from the drive letter......

Enumerating Local and Network Drives
http://access.mvps.org/access/api/api0003.htm

[June7]

What do you mean 'I don't think that I will be able to update the FE. I don't have administrator right to'? You better have rights to modify the FE. The procedure belongs in a module in the FE.

So you modified the procedure to use the Old and New path strings specific to your setup?

You can call the procedure from a button Click event or simply click the system cursor anywhere within the procedure then click 'Run' from the VBA toolbar. Since I am the only one who ever does this and very infrequently (like the last time IT replaced our server), I do the latter.

[dccjr]

Thaks for the help so far. I still have a couple of questions for June7. How did you get the code eliminating the VBScript to work. Can you give me a link to it. I would love to implement it as well. Also, I don't use a user login on the fe's, so could I check version number on my frmHome_Load event? Finally, since I am now using the tables linked, I can completely do away with all of the ADODB connection stuff, and just call the NormalizeTableLinks sub on the same Load event? Sorry if these questions seem overly simple.

[June7]

Not really sure what I did in my followup attempt that was different from before but it is working. Revised procedure:

Private Sub Form_Load()
        
'Check for updates to the program on start up - if values don't match then there is a later version
If Me.tbxVersion <> Me.lblVersion.Caption Then
    'because administrator opens the master development copy, only run this for non-administrator users
    If DLookup("Permissions", "Users", "UserNetworkID='" & Environ("UserName") & "'") <> "admin" Then
        'copy Access file
        CreateObject("Scripting.FileSystemObject").CopyFile _
            gstrBasePath & "Program\Install\MaterialsDatabase.accdb", "c:\", True
        'allow enough time for file to completely copy before opening
        Dim Start As Double
        Start = Timer
        While Timer < Start + 3
            DoEvents
        Wend
        'load new version - SysCmd function gets the Access executable file path
        'Shell function requires literal quote marks in the target filename string argument, hence the quadrupled quote marks
        Shell SysCmd(acSysCmdAccessDir) & "MSAccess.exe " & """" & CurrentProject.FullName & """", vbNormalFocus
        'close current file
        DoCmd.Quit
    End If
Else
    'tbxVersion available only to administrator to update version number in Updates table
    Me.tbxVersion.Visible = False
    Call UserLogin
End If
End Sub

You can put the version check behind whatever form you want. However, don't want the copy code to run on your development master so need some way to prevent that.

Yes, table links should eliminate need for VBA connection code.

No, don't run the NormalizeTableLinks procedure every time database opens. This procedure only needs to run when something about the directory structure changes - backend is moved or its folder is renamed - or new tables are created in the backend and those need the UNC pathing. In the case of directory structure changes, will have to manually install the modified frontend onto each user computer because the old version won't be able to find the backend.

[dccjr]

How I get the feeling that I need to modify this due to our not using log-ins for each user. Suggestions? I have not pasword protected the be or the fe for each user. I am still so new at this that I don't even know where to start for security.

[June7]

You are working on a network? BE is on server? You must login to the network? The Environ function grabs the users network login. You can use that to determine whose user network login is using the db.

The only purpose for my 'login' form is to register new users. I maintain a table of users with their network login username and the initials they like to use (no passwords, I rely on network security). We use the initials in some records. The code checks the username retrieved by Environ to see if it is in the table. If not, this is a new user and the 'login' form is exposed so they can provide their initials. That username never sees the 'login' form again. My code identifies the user by the Environ function and retrieves their initials and stores it on 'mainmenu' form. During certain data entry procedures the initials are automatically saved to record. Because it is the default open form for db, I also use the login form to manage the version number.

You can eliminate the 'login' and use your 'mainmenu' form for this procedure. You can hardcode the 'administrator' so the copy action won't execute. Doesn't matter what workstation you log onto, your network username controls.

Put the label with version number on your mainmenu form and yes, do the version check.

If Environ("USERNAME") <> "whatever your network username is" Then
'code to check for version and update if needed
End If

[dccjr]

Thank you so much. I am learning so much from you gurus on this forum (more than from most books). I really appreciate all your time and information. Thanks.