ADO: Copy Access Table into new database?

ADO: Copy Access Table into new database?

• Stephen Lepisto

  Lets see if i understand it correct
  1. you got a database with tables (db1)
  2. another db where you create the structure based on tables in db 1 (db2)
  So far it works
  
  What you could do is link the (empty) tables in db1 with a 'linked' prefix
  and run a insert query like so:
  
  Insert into Linked_DBO_mytable Select mytable.* from mytable
  
  Remco
  

• Meriya

  Thanx for the info! I've been using ADOX now, and I haven't had too much trouble with it.
  
  ---Mike
  
  

• Mike K. Clark

  Hi,

  If you already know the schema of the source table, then I suggest using SQL CREATE TABLE statements. Just store it in a text file and execute it using a OleDbCommand...

   

   

  cheers,

  Paul June A. Domag

  
  
  

• confusesc

  Mike,
  
  I've been looking for something like ADOX.NET ever since I got my beta 2 copy of Visual Studio.NET at TechEd 2001.  There currently is no substitute for ADOX if you don't already know the schema of a database.
  
  [And for all you techies out there that say "its easy, just ready the system table information!" ... If I knew the system table names on brand X database I wouldn't need something like ADOX to tell me what the system tables are!  ADOX tells you practically everything once you are connected.]
  
  So, based on over 4 years of looking Mike, there is no current replacement for ADOX in .NET or anything else.  However, I've never had a problem with ADOX messing up, so managed or unmanaged, I'll still be using it.
  
  JerryD
  
  

• Dave Hagarty

  I don't know the schema in advance; I meant I can retrieve it using ADOX. However, I'm finding that ADOX is returning the columns in alphabetical order (except for the primary key being first), and then the ADOAdapter pokes the data into the wrong columns. I might need to do some column mapping, although I assumed it defaulted to using names rather than position.
  
  Maybe ADO.NET can retrieve the table schema in correct column order, and then I *could* create the table by building an SQL command on the fly, and the ADOAdapter will update it correctly. It just seems like there is a simple way I'm missing. I searched the SQL online reference for some way to INSERT INTO from a table in a separate data store, but no joy so far.
  
  ---Mike
  

  
  

• saggett

  I'm not making myself clear. I'm doing step 2 using ADOX; my main question is should I use some other method The ADO.NET Cookbook by Bill Hamilton has a recipe for doing just that with SQL commands, but it doesn't seem to work on an OleDb database. It's also amazingly complex. So, I'm guessing the answer is no, I should continue using ADOX. (I found I did have to do a garbage collection after creating the table if I wanted to access that table quickly, even after closing everything and doing a DoEvents.)
  
  As for transferring the data, I've used linked tables a lot in VB6 (useful for connected data), but I don't understand how that helps me. The tables in db1 are not empty, they have data I want to transfer into db2. For that, I'm simply using OleDBDataAdapter.Update, which works well.
  
  So, it's all working, but I'm uncomfortable using unmanaged ADOX, although perhaps I shouldn't be.
  
  ---MikeB
  

  
  
• ADO: Copy Access Table into new database?