• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8584
  • 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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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