Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-08
16
Medium Priority
?
1,265 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 1200 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 800 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

886 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