MS Access

Posted on 2011-03-07
Last Modified: 2012-05-11
How do I link to data base using VBA Code.
Question by:PasqualeMarcantonio
LVL 75

Accepted Solution

DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
ID: 35059939
Here is the De-Facto standard:

Relink Access tables from code

You will need this also (referenced in the KB):

Call the standard Windows File Open/Save dialog box


Author Comment

ID: 35060013
I plan to open a form, test for data in a table, if the table is not found then link to the data base using VBA Code.
LVL 39

Assisted Solution

als315 earned 250 total points
ID: 35060023
Example from Access help:
DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Authors", "dboAuthors"

DoCmd.TransferDatabase acLink, "Microsoft Access", _
    "C:\My Documents\NWSales.mdb", acTable, "MyTable"
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.


Expert Comment

ID: 35060024
If you want to link to a Microsoft Access database from a standalone VB program, you can use ADODB.  The connection string will need to be specific for Access.  I'm looking for a sample and will post it when I find it.
You can also set up an ODBC connection for the MS Access database and use that in your VB for connecting.

LVL 75
ID: 35060085
There is no need to us ADODB to link to an Access MDB or ACCDB.

LVL 84
ID: 35060296
<There is no need to us ADODB to link to an Access MDB or ACCDB.>

If you mean to link tables then you're right - DAO is much easier to work with if the goal is to build linked tables from within Access.

If the OP wishes to connect to an Access database from another environment (like VB or ASP) there's nothing wrong with using ADO. In many cases it's preferable to build a connection to the Database using ADO and then work with that connection, depending on what you want to accomplish.
LVL 75
ID: 35871357
Has this question been resolved?  Can we close the question ?
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36175847
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
LVL 84
ID: 36175848
The author asked how to link tables with VBA. Both of these are correct, valid solutions:


Suggest you split the points between them
LVL 75
ID: 36176996
I concur with LSM ...


Expert Comment

by:South Mod
ID: 36230702
Following an 'Objection' by LSMConsulting (at to the intended closure of this question, it has been reviewed by at least one Moderator and is being closed as recommended by the Expert.
At this point I am going to re-start the auto-close procedure.
Thank you,
Community Support Moderator

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question