Login script that automates odbc. Scripts and batch files to EXE's to make this a bit trickier though. As for actually creating the ODBC connection. Manually create an ODBC connection on your PC using the 'Data Sources (ODBC)' applet in Admin tools. Launch regedit and go to HKLM Software ODBC and export the whole lot from ODBC down to a.reg file. This is a right click option on the folder.
- Batch File To Create Text File
- Batch File To Create Odbc Connection
- How To Write A Batch File To Create Folders
1) I would like to create a batch file or something like that, which I can send to the to my customers to automatically setup an ODBC connection. = 250 points 2) It would be even better if I could setup a macro or something like that in Access to setup the ODBC connection when a cmd button is selected.
= 250 points Since this is a two part question, I will give 250 points for each answer. The connection information is listed below. Thank you in advance for your help, Jon Select Driver: SQL Server Name: ENGPIUser Description: ENGPI User Access - Read Only Server: sql-nwss-006 sea2prod With SQL Server authentication using login ID and password entered by the user: Check Connect to SQL server to obtain default settings for additional configuration groups: Check Login ID:. Password:. Change the default database to: ENGPI Select all.
Batch File To Create Text File
Adds it on a form Open event. Option Compare Database Option Explicit Option Base 1 Private Const REGSZ = 1 'Constant for a string variable type. I already have something in the Open event, and I'm not sure how to add your code to it; I keep getting errors. Private Sub FormOpen(Cancel As Integer) On Error GoTo ErrcmdCheckOpenModeClick ' This function is useful if you have a multiuser database ' and are not using Access security or Access Run. ' This checks to see if the user has opened the database ' in exclusive mode.
If the user has opened exclusive, ' it forces an exit from the database and resets the ' user's System.mda open mode to shared globally. ' Developed by Candace Tripp, candace@tripp.net Dim OPENMODE As Integer DoCmd.Hourglass True OPENMODE = Application.GetOption(voOPENMODE) If OPENMODE = 1 Then MsgBox 'The open mode is exclusive.' , vbInformation DoCmd.OpenForm 'frmOpenedExclusive' Exit Sub Else DoCmd.Hourglass False End If ExitcmdCheckOpenModeClick: Exit Sub ErrcmdCheckOpenModeClick: DoCmd.Hourglass False MsgBox Err.Description Resume ExitcmdCheckOpenModeClick End Function Option Compare Database Option Explicit Option Base 1 Private Const REGSZ = 1 'Constant for a string variable type. Ok, I'm a looser. I am not sure where you are telling me to put Call AddENGPISystemDSN in the below On Open event code. I tried placing it at the end, but that obviously didn't work. Private Sub FormOpen(Cancel As Integer) On Error GoTo ErrcmdCheckOpenModeClick ' This function is useful if you have a multiuser database ' and are not using Access security or Access Run.
Batch File To Create Odbc Connection
' This checks to see if the user has opened the database ' in exclusive mode. If the user has opened exclusive, ' it forces an exit from the database and resets the ' user's System.mda open mode to shared globally. ' Developed by Candace Tripp, candace@tripp.net Dim OPENMODE As Integer DoCmd.Hourglass True OPENMODE = Application.GetOption(voOPENMODE) If OPENMODE = 1 Then MsgBox 'The open mode is exclusive.' , vbInformation DoCmd.OpenForm 'frmOpenedExclusive' Exit Sub Else DoCmd.Hourglass False End If ExitcmdCheckOpenModeClick: Exit Sub ErrcmdCheckOpenModeClick: DoCmd.Hourglass False MsgBox Err.Description Resume ExitcmdCheckOpenModeClick Call AddENGPISystemDSN End Sub Select all. In your form: Private Sub FormOpen(Cancel As Integer) On Error GoTo ErrcmdCheckOpenModeClick ' This function is useful if you have a multiuser database ' and are not using Access security or Access Run. ' This checks to see if the user has opened the database ' in exclusive mode. If the user has opened exclusive, ' it forces an exit from the database and resets the ' user's System.mda open mode to shared globally.
How To Write A Batch File To Create Folders
' Developed by Candace Tripp, candace@tripp.net Dim OPENMODE As Integer DoCmd.Hourglass True OPENMODE = Application.GetOption(voOPENMODE) If OPENMODE = 1 Then MsgBox 'The open mode is exclusive.' , vbInformation DoCmd.OpenForm 'frmOpenedExclusive' Exit Sub Else DoCmd.Hourglass False End If Call AddENGPISystemDSN ExitcmdCheckOpenModeClick: Exit Sub ErrcmdCheckOpenModeClick: DoCmd.Hourglass False MsgBox Err.Description Resume ExitcmdCheckOpenModeClick End Sub Select all. In the module: Option Compare Database Option Explicit Option Base 1 Private Const REGSZ = 1 'Constant for a string variable type. Did you name the module the same as the function? That is usually the problem. Can I not just add it to a cmd button, so the user can decide if they want to install it or not?
You could do it that way, or put it as the first command in the Update pull. I would advise that it be part of an automated process.
I can see a user clicking to update the data, but not clicking to add the ODBC call. Then they run it and you get a call like below. End-Luser: It doesn't work. You: What doesn't work?
End-Luser: Your ENGPI database. You: It doesn't open? End-Luser: No, it opens but when I click the button it gives me an error. You: What button? End-Luser: The button to update the data.
You: Did you click the button to add the ODBC call? End-Luser: You didn't tell me I had to click that first. You: Do you read the procedures? End-Luser: Yes You: It was in there. End-Luser: I didn't see it say that. You: Step number three.
End-Luser: I don't want to add the ODBC call. You: You need it to update the data. End-Luser: You programmers make it so complicated. Great, it worked. So I'm guessing what you are telling me here is that I should not name my modules the same thing as the one of the procedures inside of the module, right?
Ok, now that you have answered the question, is there a way to get around the users having to map the linked tables and enter the password? I was hoping that it would be all one step, but I guess not:) If there is a way to do this let me know, and I will post another question; I just don't want to waste my time posting another question is there is not a workaround. Thanks again for your help, Jon. All of the linked tables/views are working except for one.
The one that is not working is actually a linked view from another SQL Server to the ENGPI SQL Server; here is the error: - - - - - - - - - - - ODBC-call failed. MicrosoftODBC SQL Server DriverSQL ServerHetergeneous queries require the ANSINULLS and ANSIWARNINGS options to be set for the connection. This ensures consistent query semantics.
Enable these options and then reissue your query. You have to re-link the tables after you make the change. The lines below are what controls whether those three boxes are checked or not. IIRC, they are checked by default. AddSetup = 'No' '.
Hello Experts: Currently I have an access db that runs the macro to create odbc connection to connect to sql server and below is the code that I use. I need to create odbc connection through the batch (bat) file. I need help writing that bat file. Thank you for all your help. Here is the current for the module in access: Option Compare Database Option Explicit Option Base 1 Private Const REGSZ = 1 'Constant for a string variable type.