Solved

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

Posted on 2011-09-08
16
1,107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 4
16 Comments
 
LVL 120

Expert Comment

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

Expert Comment

by:Rey Obrero (Capricorn1)
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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

635 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