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?
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.
0

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

0
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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

Clive
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
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
1
Kamlesh JainManagerCommented:
i am using J Street Access Relinker and it works wonder...
Thanks
0
Armen Stein - Microsoft Access MVP since 2006PresidentCommented:
Thanks Kamlesh!  Glad you like it.  :)
0
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.