Solved

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

Posted on 2011-09-08
16
1,024 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

739 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