By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,210 Members | 1,485 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,210 IT Pros & Developers. It's quick & easy.

Import and replace table from another DB

100+
P: 547
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
Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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

100+
P: 547
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
Expert Mod 15k+
P: 31,707
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

100+
P: 547
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.