linking a split database table in Access Runtime

Question

I built a database in MS Access 2010.  The end users are only using MS Access Runtime 2013 so I split the database before I transferred it to the new PCs.  There was no way for me to link the Front End to the back in runtime.  I used some code I got from JStreet Tech, and it works if I am on the computer that has Acess 2010 installed.  However, I get a "compiling error" when I run it on any of the 3 PCs that will be using this db.

Is there a better way to do this or is there a "bullet-proof" code to add to my db?

Thanks,

Answer 1

Albert,  Thank you so much for trying to help with this.  I continue to have issues, though.  In fact, out of frustration, I started over with a saved copy of my original database.  Here are the steps I took and the result...

1. Opened original database (There is no code in the original)
2. Split the database
3. Opened the front end of the database and verified that the front end was linked to the back end.
4. Clicked on Create Module...
5. Module window opened to "Option Compare Database"
6. Place cursor below first line and pasted the code from the link provided
7. Saved the Module1
8. Clicked Debug  -> Compile
9. Got the following error:  Compile Error:  Sub or Function not Defined
10. The following code was highlighted... ahtAddFilterItem
11. The complete line of code for that is strFilter = ahtAddFilterItem(strFilter, _
12. The complete section this came from is...

Function fGetMDBName(strIn As String) As String

'Calls GetOpenFileName dialog

Dim strFilter As String

    strFilter = ahtAddFilterItem(strFilter, _

                    "Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _

                    "*.mdb; *.mda; *.mde; *.mdw")

    strFilter = ahtAddFilterItem(strFilter, _

                    "All Files (*.*)", _

                    "*.*")

    fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _

                                OpenFile:=True, _

                                DialogTitle:=strIn, _

                                Flags:=ahtOFN_HIDEREADONLY)

End Function

Ideas?  What am I doing wrong?

Answer 2

Not really clear why you can’t compile. And for runtime you ALWAYS want to distribute an accDE (compiled application). You certainly never want to distribute or “try” an application with compile errors with the runtime. In fact any un-handled error will shut down the runtime with an accDB. However with a compiled accDE, the runtime is bullet proof and runtime errors will not shutdown the application, but even un-handled errors NEVER re-set your VBA variables and NEVER shutdown the application. So for safe runtime use, you only want to use a compiled accDE.

Assuming your application compiles now (before importing j-link code), then ONLY after your sure the existing application compiles ok, you then simply import the VBA code module from that j-link sample. In Access from the external data tab in the ribbon, simply click on Access and follow the prompts and browse to JStreeeAccessRelinker.mdb.

You then import the code module. The result would be that you now have a NEW code module called

basJtreeAccesRelinker.

At this point, try another compile. Your code should continue to compile.

Once above works, then you can have your start up code call the re-link code, or use the supplied autoexec macro.

So either add (or import) the Auto Exec macro, or in your VBA start up code simply place this one line of code:

   jstCheckTableLinks_Full

So it is not clear, but assuming you start with your application.

#1 Ensure debug compile works BEFORE you import the jstreet re-link code.

#2 Then import the one code module from the sample re-link application into your application.

#3  Again, do a debug-compile. This will ensure that the imported code has no errors.

#4

Once the above compile works, then consider using the Auto Exec macro, or as noted add the one line of VBA to your start up code to “check” if tables need to be re-linked.

Post back on as to which of the above 3 steps fail for you.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 3

I loaded the code into our database and I couldn't compile it on the PC that I've built this on that has MS Access 2010 installed.  When I attempt this I get an error:

Compile Error

Sub or function not defined

This was the part of the code that was highlighted - jstCheckTableLinks_Prompt

Just for giggles I put it on the laptop that has the runtime 2013 Access version.  At least this time, I was able to load the application.  However, when I clicked on the button to run this I got an error as well... Execution of this application has stopped due to a run-time error  This application cannot continue and will be shut down

I've tried putting the code in as a module and as a function of the button to bring up the linked table manager.

Any thoughts???

Answer 4

Where exactly should I put that code found in the link you provided?

Answer 5

Tom,

I tried both files types and got the same result.