Solved

How to convert a linked table to a local table via code.

Posted on 2011-09-08
16
913 Views
Last Modified: 2013-11-29
How can I use code to convert a linked table to a local table (structure and data)?
0
Comment
Question by:Milewskp
  • 6
  • 6
  • 4
16 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36504869
you have to create a make table query using your linked table as  the source
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36504939
in vba code

currentdb.execute "SELECT LinkedTableName.* INTO LocalTable FROM  LinkedTableName"
0
 
LVL 12

Accepted Solution

by:
danishani earned 300 total points
ID: 36505070
If it's from an Access database try something like this:

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\YourPath\YourDatabaseName.accdb", acTable, "tblYourImportTable", "tblNewTable", False

HTH,
Daniel
0
 
LVL 1

Author Comment

by:Milewskp
ID: 36505127
Hi cap,
<you have to create a make table query using your linked table as  the source>
This will copy the data, not the table (eg validation rules and formatting will not appear in the new table).

Hi danish,
DoCmd.TransferDatabase will copy a linked linked to a linked table. I need to copy a linekd table to a local table.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36505230
if you want to get all those properties you have to import the table from the SOURCE db of the linked table.
0
 
LVL 12

Expert Comment

by:danishani
ID: 36505248
Hi again,

Not if the Table is from an other Database as stated above. It will import the Table with Structure and Data in your current database.

Anyway, you might then create a two step approach as Capricorn suggested:
1. Create a Table with SQL "CREATE TABLE etc."
2. AppendQuery with SQL "SELECT ... INTO ... FROM ..."

Hope this helps,
Daniel
0
 
LVL 1

Author Comment

by:Milewskp
ID: 36505283
Hi danish,
Just my luck - in my case both tables are in the same db.
Can you explain how to use CREATE TABLE to copy a linked table to a local?

I'll thinking that there must be a simpler way to replicate the manual Copy, Paste actions in code...
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36505315
<Just my luck - in my case both tables are in the same db.>
what do you mean ?
a linked table must have a SOURCE database, not the same database.

right click on the linked table > Link table manager

you will see the path to the original table...
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 12

Expert Comment

by:danishani
ID: 36505365
I understand they are located in the same db, but what is the Source of the Linked Table located at?
Is that also an Access Database? If so, you can do this with the TransferDatabase Method as stated above.

The CREATE TABLE is actually a long road to Create a Duplicate of your existing one.

You might find this Function interesting, using DAO, found in this thread:
http://stackoverflow.com/questions/1594096/how-to-copy-a-linked-table-to-a-local-table-in-ms-access-programmatically

 
Public Sub CopySchemaAndData_DAO(SourceTable As String, DestinationTable As String)
On Error GoTo Err_Handler

Dim tblSource As DAO.TableDef
Dim fld As DAO.Field

Dim db As DAO.Database
Set db = CurrentDb

Set tblSource = db.TableDefs(SourceTable)

Dim tblDest As DAO.TableDef
Set tblDest = db.CreateTableDef(DestinationTable)

'Iterate over source table fields and add to new table
For Each fld In tblSource.Fields
   Dim destField As DAO.Field
   Set destField = tblDest.CreateField(fld.Name, fld.Type, fld.Size)
   If fld.Type = 10 Then
      'text, allow zero length
      destField.AllowZeroLength = True
   End If
   tblDest.Fields.Append destField
Next fld

'Handle Indexes
Dim idx As Index
Dim iIndex As Integer
For iIndex = 0 To tblSource.Indexes.Count - 1
   Set idx = tblSource.Indexes(iIndex)
   Dim newIndex As Index
   Set newIndex = tblDest.CreateIndex(idx.Name)
   With newIndex
      .Unique = idx.Unique
      .Primary = idx.Primary
      'Some Indexes are made up of more than one field
      Dim iIdxFldCount As Integer
      For iIdxFldCount = 0 To idx.Fields.Count - 1
         .Fields.Append .CreateField(idx.Fields(iIdxFldCount).Name)
      Next iIdxFldCount
   End With

   tblDest.Indexes.Append newIndex
Next iIndex

db.TableDefs.Append tblDest

'Finally, copy data from source to destination table
Dim sql As String
sql = "INSERT INTO " & DestinationTable & " SELECT * FROM " & SourceTable
db.Execute sql

Err_Handler:
   Set fld = Nothing
   Set destField = Nothing
   Set tblDest = Nothing
   Set tblSource = Nothing
   Set db = Nothing

   If Err.Number <> 0 Then
      MsgBox Err.Number & ": " & Err.Description, vbCritical, Err.Source
   End If

Open in new window

0
 
LVL 1

Author Comment

by:Milewskp
ID: 36505553
Hi cap,
I was using  TransferDatabase  to copy the linked table in the current database. If I copy the table in the foreign database, then it works as you say (thanks).
How would I then replace the old linked table with the new local table?
0
 
LVL 1

Author Comment

by:Milewskp
ID: 36505755
Hi Danish,
<Is that also an Access Database? >
Yes
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 total points
ID: 36505767
just drop the linked table before importing the original table


currentdb.execute "drop table LinkedtableName"

or

docmd.deleteobject actable, "LinkedtableName"
0
 
LVL 12

Expert Comment

by:danishani
ID: 36505818
Hi Danish,
<Is that also an Access Database? >
Yes

That means that this statement should work, no need to do anything else then:

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\YourPath\YourDatabaseName.accdb", acTable, "tblYourImportTable", "tblNewTable", False

Note: that the path is the Access Database where you Import the table from, and will import the table from this database into the current database. This will not link the table as you suggested, which uses the acLink option instead.

I have tested this myself, and it works as aspected.

Hope this helps,
Daniel

0
 
LVL 1

Author Comment

by:Milewskp
ID: 36506063
hi cap,
<currentdb.execute "drop table LinkedtableName" or docmd.deleteobject actable, "LinkedtableName">
is there any advantage to one other the other?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36506315
no, it will do the same thing, delete the table...
0
 
LVL 1

Author Comment

by:Milewskp
ID: 36525828
Thanks everyone for your help; the solution I settled on is attached.

I will split the points.
DoCmd.TransferDatabase acImport, "Microsoft Access", strConnect, acTable, MyLinkedTableName, MyLocalTableName
RQ "DROP TABLE [" & MyLinkedTableName & "]"
CurrentDb.TableDefs(MyLocalTableName).Name = MyLinkedTableName

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

948 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now