none
bulk 'Insert' to MS Access Table Select from Other MS Access Table

    Question

  • Hi, I have a problem and Thanks for a solution.

    for example I have a datatable from OldDB and insert all the data to newDB, it did not work in the first UpDataDB(string newDB, string OldDB), but I have to manaully assign the data and works on the second UpDataDB(string newDB, string OldDB), but I think it is not the right solution. Could you please help me find out why the first one not working? Thanks.

     

    public static void UpDataDB(string newDB, string OldDB)

    {

    DataTable DTG = new DataTable();

    OleDbConnection conn = new OleDbConnection();

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + OldDB;

    string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newDB;

    string SQL = "SELECT * FROM Location ";

    string INSERT = "INSERT INTO Location (sLocationID, sDescription) " +

    "VALUES (@sLocationID, @sDescription)";

    try

    {

    OleDbDataAdapter adapterLocation = new OleDbDataAdapter(SQL, conn);

    conn.Open();

    adapterLocation.Fill(DTG);

    conn.Close();

    DataTable DT = new DataTable();

    OleDbConnection OleConn = new OleDbConnection(ConnString);

    OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);

    OleAdp.InsertCommand = new OleDbCommand(INSERT);

    OleAdp.InsertCommand.Parameters.Add("@sLocationID", OleDbType.VarChar, 10, "sLocationID");

    OleAdp.InsertCommand.Parameters.Add("@sDescription", OleDbType.VarChar, 255, "sDescription");

    OleAdp.InsertCommand.Connection = OleConn;

    OleAdp.InsertCommand.Connection.Open();

     

    OleAdp.Update(DTG);

    OleAdp.InsertCommand.Connection.Close();

    }

    catch (Exception ex)

    {

    }

    }

     

     

    Second method, it is working but it should not be right solution:

     

    public static void UpDataDB(string newDB, string OldDB)

    {

    DataTable DTG = new DataTable();

    OleDbConnection conn = new OleDbConnection();

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + OldDB;

    string ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newDB;

    string SQL = "SELECT * FROM Location ";

    string INSERT = "INSERT INTO Location (sLocationID, sDescription) " +

    "VALUES (@sLocationID, @sDescription)";

    try

    {

    OleDbDataAdapter adapterLocation = new OleDbDataAdapter(SQL, conn);

    conn.Open();

    adapterLocation.Fill(DTG);

    conn.Close();

    DataTable DT = new DataTable();

    OleDbConnection OleConn = new OleDbConnection(ConnString);

    OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);

    OleAdp.InsertCommand = new OleDbCommand(INSERT);

    OleAdp.InsertCommand.Parameters.Add("@sLocationID", OleDbType.VarChar, 10, "sLocationID");

    OleAdp.InsertCommand.Parameters.Add("@sDescription", OleDbType.VarChar, 255, "sDescription");

    OleAdp.InsertCommand.Connection = OleConn;

    OleAdp.InsertCommand.Connection.Open();

    OleAdp.Fill(DT);

     

    for (int i = 0; i < DTG.Rows.Count; i++)

    {

    DataRow custRow = DT.NewRow();

    custRow["sLocationID"] = DTG.RowsIdea["sLocationID"];

    custRow["sDescription"] = DTG.RowsIdea["sDescription"];

    DT.Rows.Add(custRow);

    }

    OleAdp.Update(DT);

    OleAdp.InsertCommand.Connection.Close();

    }

    catch (Exception ex)

    {

    }

    }

    Wednesday, April 11, 2007 9:07 PM

Answers


  • Below is an example of a SQL statement that you can execute which will export data from a table in one Access database to a table in another Access database. The assumption here is that the tables have the same structure. If this is not the case then we'll have to tweak the SQL statement to include the column names:

     

    INSERT INTO [AccessTable] SELECT * FROM [MS Access;DATABASE=D:\My Documents\db2.mdb].[Table2]

    Wednesday, April 11, 2007 11:11 PM
  • Yes, but the syntax will depend upon whether you're importing into an existing table or creating a new table during the import. For example, the following will create the table in Access during the import:

     

    SELECT * INTO [Data] FROM [Excel 8.0;DATABASE=E:\My Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]

     

    The below statement operates on an existing table in Access:

     

    INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4 FROM [Excel 8.0;DATABASE=E:\My Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]

     

    In each example above the connection should be made to the Access database in order to perform the import.

    Thursday, January 10, 2008 5:07 PM
  • Just add the PWD argument:

     

    [MS Access;DATABASE=D:\My Documents\db2.mdb;PWD=password].[Table2]

     

    Wednesday, February 13, 2008 3:04 PM

All replies


  • Below is an example of a SQL statement that you can execute which will export data from a table in one Access database to a table in another Access database. The assumption here is that the tables have the same structure. If this is not the case then we'll have to tweak the SQL statement to include the column names:

     

    INSERT INTO [AccessTable] SELECT * FROM [MS Access;DATABASE=D:\My Documents\db2.mdb].[Table2]

    Wednesday, April 11, 2007 11:11 PM
  • That works ,

    Thanks a million for your time.
    Friday, April 13, 2007 8:00 PM
  • Hi,

     

    Is there any similar way to insert records from a spread sheet into a Access table?

     

    Thanks,

    Madheshwarn.R.

    Thursday, January 10, 2008 11:28 AM
  • Yes, but the syntax will depend upon whether you're importing into an existing table or creating a new table during the import. For example, the following will create the table in Access during the import:

     

    SELECT * INTO [Data] FROM [Excel 8.0;DATABASE=E:\My Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]

     

    The below statement operates on an existing table in Access:

     

    INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4 FROM [Excel 8.0;DATABASE=E:\My Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]

     

    In each example above the connection should be made to the Access database in order to perform the import.

    Thursday, January 10, 2008 5:07 PM
  • Hi,

    Many thanks for your quick response. It works perfectly.

     

    However all my date fields are replaced by Null. Can you please help me to resolve this? I have around 20K records in the Excel almost all rows have valid date's, unfortunately none of the values are converted.

     

    Also I have one more clarification - My VBA code takes ages to convert 20K records which reads cell by cell and prepares the SQL stmt and inserts using CurrentDB.Execute method. But this bulk insert takes only a minute. Is there any way to improve the VBA code ?

     

    Thanks,

    Madheshwaran.R.

     

    Thursday, January 10, 2008 7:54 PM
  • Could you post the SQL code you are using?

     

    With respect to the method you're using with VBA I don't believe you will be able to improve performance very much. The automation methods are inherently slow.

     

    Friday, January 11, 2008 12:39 PM
  • What if the file D:\My Documents\db2.mdb is password protected?
    How do you incorporate the password in the string?

    Thanks,
    Mark Oberg
    Wednesday, February 13, 2008 2:44 PM
  • Just add the PWD argument:

     

    [MS Access;DATABASE=D:\My Documents\db2.mdb;PWD=password].[Table2]

     

    Wednesday, February 13, 2008 3:04 PM
  • Where is this type of information documented?

    Thanks,
    Mark Oberg
    Wednesday, February 13, 2008 3:07 PM
  • Not in any one place. ODBC technology and the MS Access driver is also somewhat dated technology so it might be difficult to find Microsoft documentation that covers this. I'm familiar with it because I've been working with it for quite a while.

     

    Wednesday, February 13, 2008 3:46 PM
  • i want to update one access database with another access database. similar table names. i tried the INSERT INTO[DATA] FROM .........

    it gives me error, saying it doesnt support duplicate. how do i update the table with some data already in it?

    Thursday, April 10, 2008 5:39 AM
  • Does the destination table have a primary key constraint. It sounds like that may be what is generating your error message.

     

    If this is the case you may want to post your full SQL statment and indicated which columns comprise your primary key.

    Thursday, April 10, 2008 8:11 PM
  • basically what im trying to do here is update the tables in the master database with the updated data from similar tables but in different databases. the whole project is to update the master database with updated info. from different databases. so the updating process should be triggered. i suppose i dont have to fill data into DATASET because im not making any changes to it. i just want to extract it and pass it directly to the main database.

     

    when i use the following code it says duplicates will be made! but i just want to overite the existing or update it.

    the primary key for the table is staff_id. help will be highly appreciated.

     

    Dim AccessCommand As OleDbCommand = New OleDbCommand("INSERT INTO [Staff] SELECT * FROM [MS Access;DATABASE=C:\RetailM\Clearwater Pty Ltd\Recent.mdb].[Staff]", objAccessConn)

    AccessCommand.ExecuteNonQuery()

    objAccessConn.Close()

    Catch ex As Exception

    MessageBox.Show(ex.Message)

    Finally

    objAccessConn.Close()

    End Try

    Friday, April 11, 2008 7:09 AM
  • It is possible to insert rows from one table that are 'not in' another (with respect to a primary key) using a single SQL statement. I can provide an example if you need to do that. However, you can't simply update/overwrite existing data using the same type of query. To do this you will either need to use Replication (which is supported for Access databases) or you will need to write code to iterate through each row in the source table, check the destination table based upon the primary key and then Add or Update the row depending upon whether it exists.

    A third method that may work for you is the DataSet Merge method:

    http://www.knowdotnet.com/articles/datasetmerge.html



    Friday, April 11, 2008 12:24 PM
  • thats exactly what i need to do man. can u hook me up with a sample code that iterate thru each row. im realy new to vb.net and im kinda runnin behind schedule. i really appreciate ur help man. cheers.

    Saturday, April 12, 2008 9:19 AM
  • SELECT * INTO [Data] FROM [Excel 8.0;DATABASE=E:\My Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]

     

    Is it possible to make the above alo valid for a text file tab delimitered (*.tab)? or a csv file?

    Wednesday, August 20, 2008 12:41 PM
  • Yes, it's possible but I don't understand exactly what you need to do.

     

    If the file is tab delimited then a schema.ini file is required to indicate that the delimiter is a tab.

     

    Wednesday, August 20, 2008 2:03 PM
  • I want to use this file/data in Access.

    My idea was to create a table in Access en dump the file in it.

    Via a Recordset it takes a long time, I hope this will speed up (because writing this file and importing it manually in Access is faster...but not automated).

     

    Wat is the correct syntax then for this Select...Into statement?

    Wednesday, August 20, 2008 2:11 PM
  • Below is an example:

     

    Code Snippet

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=C:\Test Files\db1 XP.mdb")

     

    AccessConn.Open()

     

    'New table

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [TextFile] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[TextFile.txt]", AccessConn)

     

    'Existing table

    'Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [TextFile] (F1, F2, F3, F4, F5) SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=C:\Documents and Settings\...\TextFiles;].[TextFile.txt]", AccessConn)

     

    AccessCommand.ExecuteNonQuery()

    AccessConn.Close()

     

     

    A schema.ini file would contain the following (assuming the first line of the file has column headers):

     

    [TextFile.txt]
    ColNameHeader=True
    Format=TabDelimited
    CharacterSet=ANSI

    Wednesday, August 20, 2008 4:02 PM
  • What if the other Access database is located in a server?

     

    Friday, November 21, 2008 11:58 PM
  • If the Access database is on another machine then specify the UNC path to the file (don't use a mapped drive letter to the resource). Keep in mind that users may require full permissions to the folder where the database is located in order for the corresponding .LDB file to be created and managed by the Jet database engine.

    Saturday, November 22, 2008 12:06 AM
  • Hello,
    How would one do this with a visual foxpro table that uses a database container (.dbc)?  I am using access 2007.

    Any help would be wonderful, thanks!
    Friday, February 20, 2009 6:24 PM
  • Can you create a linked table to the Visual FoxPro database in Access? That would allow you to transfer directly from the Access table to Visual FoxPro or vice versa.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, February 23, 2009 3:30 PM
  • Hi Paul,
    Thanks for responding. That would be the easiest and most straightforward way to do it, however, when I link the table, Access complains that a "Record is too large" and the table contents don't show.  I can't alter the linked table, as it belongs to someone else.

    I am hoping there is some way to create an access query using the methods mentioned so that I may use sql to select the few fields I need. Those fields don't cause the "Record is too large" error. 

    It would be awesome if I could do something like this (doesn't actually work of course, gives a "Could not find installable ISAM" error):

    SELECT caadsas, cfname, left(cmname,1), clname, cssnum, dbirth, coffstat, doffstat, ccust_txt, cemail, '1/1/' + left(caadsas,4) AS class_level
    FROM [Microsoft Visual Foxpro;DATABASE=O:\PharmAdmit2009Folder\2009WorkingDataFolder\data\WORKING\admit.dbc].[appmain];


    Thanks for any help or suggestions with this issue!
    Thursday, February 26, 2009 7:32 PM
  • Are one of the columns a BLOB or MEMO data type? If so you probably won't be able to transfer the data directly via SQL. The limitation for SQL would be working with any column where the data exceeds 255 characters.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, February 26, 2009 7:56 PM
  • I took a look and some of the columns in the table are blob or memo data type, however, the columns that I require in the select statement are neither and they do not exceed 255 chars in length.
     
    Therefore I assume I won't have the "Record is too large" error, if I were to get the select statement working.

    I tried the following connection strings to see if it would work (but they don't):
    SELECT caadsas, cfname, left(cmname,1), clname, cssnum, dbirth, coffstat, doffstat, ccust_txt, cemail, '1/1/' + left(caadsas,4) AS class_level
    FROM [Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=O:\PharmAdmit2009Folder\2009WorkingDataFolder\data\WORKING\admit.dbc;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO]

    SELECT caadsas, cfname, left(cmname,1), clname, cssnum, dbirth, coffstat, doffstat, ccust_txt, cemail, '1/1/' + left(caadsas,4) AS class_level
    FROM [Provider=vfpoledb.1;Data Source=O:\PharmAdmit2009Folder\2009WorkingDataFolder\data\WORKING\admit.dbc;Collating Sequence=machine]

    Both give me the same error: "The Microsoft Office Access Database engine cannot find the input table or query. Make sure it exists and that its name is spelled correctly."

    I've also noticed that none of the connection strings I encountered on my web search include a way to open up a foxpro table (.dbf), only the container (.dbc).  I wonder if it is even possible to link a foxpro table in this manner.  Any thoughts?

    Thanks for your help Paul.
    Thursday, February 26, 2009 8:51 PM
  • If you have a .dbc then you should be using the ODBC driver to link to FoxPro from Access:

    How to use Visual FoxPro DBC files in Access 2002


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, February 27, 2009 7:04 PM
  • Thanks Paul.  As I said, I've already tried the conventional method of import/link the foxpro table directly into access (with the odbc driver for the dbc).  However, I encounter the "Record too large error" because some of the fields are Memo data type.  That is why I am hoping I might use a select statement + connection string in an access query.  It would allow me to select only the fields that i need (none of which are Memo or Blob data type).

    I am beginning to think it is not possible to link a foxpro table in this method, as I have never seen it done before.
    Monday, March 02, 2009 10:29 PM
  • I don't a see a way to connect to the FoxPro database through the SELECT statement. It's possible that it may not be supported by the driver.

    I was able to connect with the ODBC driver and OLEDB provider through a Connection object, so you should still be able to perform the import - you'll just have to do it row by row.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Wednesday, March 04, 2009 4:46 PM