Solved

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

Posted on 2011-09-08
16
931 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 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

825 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