February 14, 2012 at 8:42 am
Okay. Did that, but still can't connect to the tables. Not even sure how to access the module once it's saved.
February 14, 2012 at 8:49 am
I try to launch the module from the On Open event of a form and get the following error:
The expression On Open you entered as the event property setting produced the following error: Cannot define a Public user-defined type within an object module.
February 14, 2012 at 8:59 am
I am not familiar with that OnOpen event method. I think that was Stephen_W_Dodd's method.
My method is a manual process to update the tables when changes are made to the back-end.
There are two modules, one that holds the code and another that you access when you want to run the code. The second module is really a convenient place to put some comments and stage your "FixConnections' line and a place from which you then can access the immediate window and paste in the "FixConnections' text and manually run the code in the immediate window by hitting enter.
Accessing the modules: http://www.techonthenet.com/access/database/display_modules2007.php
February 14, 2012 at 9:07 am
gotcha. I guess I need to find out how to make the connection to the back end when the front end is first opened.
February 14, 2012 at 9:16 am
With the method I describe the connections are persistent and I only manually invoke the code when I am making design changes on the backend. In a development phase I am fixing the connections all the time, later on not so much.
The other advantage here is you do not need to worry about creating or maintaining DSNs. So when you asked the best way to create a linked table, I offered a way that will work without DSNS and a way that you can quickly update the linked tables if you make a backend change.
Do you have a requirement to update the linked tables everytime the app is opened?
February 14, 2012 at 9:29 am
Nope, that's what I want to do. I guess I'm not understanding how the module is called to make the connection to the back end when the front end is opened. Is the module automatically fired when the access FE is opened?
February 14, 2012 at 9:44 am
>> Is the module automatically fired when the access FE is opened?
Not in the method I describe. Its more of an access question so you might have better luck in a access forum.
This looks somewhat promising for your needs: http://access.mvps.org/access/tables/tbl0009.htm
February 14, 2012 at 10:00 am
Thanks! Whether I can get this to work or not, I really appreciate all your help. I'm trying to learn this as I go, and somethings are easier to accomplish than others!
February 14, 2012 at 3:12 pm
>> Is the module automatically fired when the access FE is opened?
To get something to fire whenever the FE is opened, there is a special macro named "autoexec". Anything you want to run on startup can be called from that macro. I usually have a procedure named "InitApp" where I put all my startup checks.
February 15, 2012 at 10:15 am
Gotcha, thanks. Unfortunately I can't figure out how to get the connection to the backend so I think I'm stuck with a DSN connection on each workstation. Bloooows.
February 15, 2012 at 10:48 am
actually, you could create a "file" dsn using the ODBC data source wizard. The wizard will want to save to "C:\Program Files\Common Files\ODBC\Data Sources" (at least on my box), but you can choose any path. Choose Windows authentication in the ODBC setup. Then use linked table manager in Access to link using ODBC and point to the file dsn that you juct created. This seems to store the link data in the mssysobjects table within access and your app is portable - as long as the person logged on is a member of the AD group that has access to your SQL Server. If you open the file dsn in notepad, it should look something like:
[ODBC]
DRIVER=SQL Server Native Client 10.0
UID=<your windows login>
Network=DBMSSOCN
DATABASE=<your sql DB>
WSID=<your machine name(not needed)>
APP=Microsoft Data Access Components
Trusted_Connection=Yes
SERVER=<Server name>
Description=test
February 15, 2012 at 1:57 pm
I actually wondered if that would work because I had deleted the dsn file and was still able to access the back end. I pushed the front end to someone and they couldn't access the back end, even with the proper permissions on the SQL server. Turns out I need to have them install the SQL2008 Native Client. Once they did that it was instant access! Now I just need the dsn file on my machine for dev and relinking purposes, and then I can push out updated front ends to users without needing to do anything else.
Not exactly the smoothest way to go about this, but it seems like it should work pretty well.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply