• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8594
  • Last Modified:

Use VBA code to relink / change linked tables.

Hi,
Is there anyway I can use VBA code in MS ACCESS to remove a linked table and connect a linked table?

I need the linking code (or procedure if it's big enough) to go in the following:

UNLINKTABLE("tbl_main", "C:\date\20060426\exampe.mdb") 'unlink yesterday's table, inorder to get new data.

If Exists "C:\date\20060427\exampe.mdb" then 'if file is available
     LINKTABLE("tbl_main", "C:\date\20060427\exampe.mdb") 'link today's table
Else 'if file is not available
     LINKTABLE("tbl_main", "C:\date\20060426\exampe.mdb") 'link to yesterday's table
End If
0
Marcarobinson
Asked:
Marcarobinson
  • 2
1 Solution
 
dmitryz6Commented:
Dim db As DAO.Database, tbl As DAO.TableDef,s as string
   Set db = CurrentDb
   set tbl = db.TableDefs("tbl_main")
if len(dir("C:\date\20060427\exampe.mdb"))>0 then
        s = ";DATABASE=" & "C:\date\20060427\exampe.mdb"
       
  else
        s = ";DATABASE=" & "C:\date\20060426\exampe.mdb"

end if
tbl.Connect = s
tbl.RefreshLink
0
 
MarcarobinsonAuthor Commented:
I get the error "User-defined type not defined" and it highlights the "db as DAO.Database" part of the code.
0
 
dmitryz6Commented:
you need to add DAO library

Open code ->Tools->References->Check Microsoft DAO 3.6
0
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now