How do I create a link from the front end to a table in the back end.

I have created a table in the back end database using VBA in the front end.

How do I create a link in the front end to the new table?

Thanks in advance

Clive
Clive BeatonAccess DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the Linked Table Manager in Access to do this. In 2016 that's on the External Data >> Import and Link ribbon section. It's in similar locations in other versions of Access. Just follow the prompts to link your table.

You can also do this in VBA if you'd prefer. To do that use TransferDatabase with the acLink TransferType. If the source is another Access database:

DoCmd.TransferDatabase acLink, "Microsoft Access", "Full path to your database", acTable, "SourceTableName", "DestinationTableName"

https://msdn.microsoft.com/en-us/vba/access-vba/articles/docmd-transferdatabase-method-access

You can also create a TableDef in your database and set the Connect property of that Tabledef.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You can do like this:

DoCmd.TransferDatabase acLink, "Microsoft Access", "d:\path\backend.accdb", acTable, "TableToLink", "LocalTableName"

Open in new window

Clive BeatonAccess DeveloperAuthor Commented:
I don't know if this is important but the table only exists in the back end.

With transferdatabase:

1. Does the table have to exist in the front end?
2. Where do I specify the password for the back end?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
If so, you may need the method described here:

Docmd.TransferDatabase with a password-protected database
Clive BeatonAccess DeveloperAuthor Commented:
Ignore my last question.  Problem solved.  Thanks to you both.

Clive
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Hi Clive,

If you keep a persistent link to that table you created, you also need to relink when the location of the back-end database changes.  You're welcome to use our free J Street Access Relinker on our J Street Downloads page.  It's a lot nicer than the Linked Table Manager.

http://www.JStreetTech.com/downloads

It's some code that you simply copy into your front-end application.  It handles multiple Access back-end databases, ignores non-Access tables, and can automatically and silently relink to back-end databases in the same folder as the application (handy for work databases or single-user scenarios).  There's a ReadMe table with instructions.

Cheers,
Armen
Kamlesh JainManagerCommented:
i am using J Street Access Relinker and it works wonder...
Thanks
Armen Stein - Microsoft Access MVP since 2006President, J Street TechnologyCommented:
Thanks Kamlesh!  Glad you like it.  :)
Clive BeatonAccess DeveloperAuthor Commented:
Hi Armen,

Thanks for that.  I wish I had it years ago.  It would have made life so much easier.

Clive
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.