Copy tables from MS access to MSSQL

[07465435]

Hi i am very stuck there.

I am trying to create an vb.net program to transfer or copy tables from my access database to an MSSQL DB.

I got it working to copy from MSSQL to access but not the other way around.

Thanks in advance for helping

[jg007]

Please post your example code here and any errors received .

the code to copy from one to the other would probably be pretty much the same for either direction or at least it was when I was using access and MS SQL server

[07465435]

Thanks and sorry should of posted the code first lol


The code is

                Dim Cmd As OleDbCommand
                Dim SQL As String
                Dim objCmd As New OleDbCommand
                Dim Con = New OleDbConnection("Provider=SQLOLEDB;" & db_conn_string)
 
SQL = "SELECT * INTO [" & lst_restor_list_confirm.Items(count) & "] FROM [odbc;Driver={Microsoft Access Driver (*.mdb)};Dbq=" & frm_restore_db.OpenFileDialog1.FileName & ";Uid=;Pwd=;].[" & lst_restor_list_confirm.Items(count) & "]"
 
Cmd = New OleDbCommand(Sql, Con)
 
objCmd = New OleDbCommand(SQL, Con)
Con.Open()
objCmd.ExecuteNonQuery()
 
Con.Close()
 
 

and the error i get is


System.Data.OleDb.OleDbException was unhandled

ErrorCode=-2147217865
Message="Invalid object name 'odbc;Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db_backup_15-02-2009.mdb;Uid=;Pwd=;.tb_user'."
Source="Microsoft OLE DB Provider for SQL Server"

if it helps the sql statment shows as below when the code is running

SELECT * INTO [tb_user] FROM [odbc;Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db_backup_15-02-2009.mdb;Uid=;Pwd=;].[tb_user]


any help you can give is thanked big time

[jg007]

bit difficult to work out without the database but did you mean to have the connection string and the select statement together like that as it does not seem right

[nukefusion]

jg007 is right. Your SELECT statement is incorrect. You have your second connection string where the table name should be.

[07465435]

what something like

SELECT * INTO [mssql_database_connecting].[tb_user] FROM [odbc;Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db_backup_15-02-2009.mdb;Uid=;Pwd=;].[tb_user] somthing like that

[nukefusion]

No, as far as I know you can't use an SQL statement in that way. You can move things between different databases, say when using a single connection to a single server, but you're trying to move things between two different database types and therefore two different connections.

You'll need to connect to your access database, SELECT out your data into a DataTable object or something. Close the access connection. Open a connection to SQL server, then copy the data from the DataTable object to the SQL database using an INSERT statement.

[07465435]

ok np i give that an go then thanks for your help