469,149 Members | 1,857 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,149 developers. It's quick & easy.

Import and replace table from another DB

547 512MB
I want to replace an existing table in my application from another DB.
I use the following code, but if the table exists in my main application,then it creates another table ie facility1 instead of replacing it with the structure and data from other table been imported.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\pisprg\Pisdatav1.accdb", acTable, "FacilityT", "FacilityT", False
any suggestions please?
Dec 1 '11 #1
4 15821
TheSmileyCoder
2,321 Expert Mod 2GB
Yes, simply delete it before running that bit of code. This code will delete the table (Without warning!)
Expand|Select|Wrap|Line Numbers
  1. Public Sub DeleteTable(strTableName As String)
  2.     'Enable in line error handling
  3.         On Error Resume Next
  4.  
  5.     'Try to delete table
  6.         DoCmd.DeleteObject acTable, strTableName
  7.  
  8.     'Check if errors occured
  9.         If Err.Number = 0 Then
  10.             'No errors
  11.         Else
  12.             If Err.Number = 7874 Then
  13.                 'Table not found, not a problem,it was probably deleted at some other point
  14.             ElseIf Err.Number = 2008 Then
  15.                 MsgBox "Table " & strTableName & " is open, cannot be deleted"
  16.             Else
  17.                 MsgBox Err.Number & " - " & Err.Description
  18.             End If
  19.         End If
  20.  
  21.     'Resume normal error handling
  22.     On Error GoTo 0
  23. End Sub
  24.  
Dec 1 '11 #2
neelsfer
547 512MB
thx smileycoder.
That makes sense to delete first. Just one question - where in your code will i add my code? And if i have 3 tables to delete, how would i incorporate it in same code?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\pisprg\Pisdatav1.accdb", acTable, "FacilityT", "FacilityT", False
Dec 2 '11 #3
NeoPa
32,165 Expert Mod 16PB
It would go (as a call) before your line of code :

Expand|Select|Wrap|Line Numbers
  1. DeleteTable "FacilityT"
  2. DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\pisprg\Pisdatav1.accdb", acTable, "FacilityT", "FacilityT", False
Smiley's code itself would be housed in a standard module (It's already declared as Public, which is generally necessary).
Dec 3 '11 #4
neelsfer
547 512MB
thx Neopa will try it today
Dec 3 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Mike C# | last post: by
reply views Thread by oiref | last post: by
2 posts views Thread by GarryJones | last post: by
4 posts views Thread by SirCodesALot | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.