a quick way to move data from one database to another

I need to write a little program that reads data from one access database into sql server database. for the record, the two database have similar database with similar fields except that the sql database has two extra fields in each table.

I can read from each access table and write that info into sql tables, but i think it will be long because i have to code all the field parameters. and some table has more that 45 fields. it might take long.

I thank you in advance.

Comments

  • seancampbellseancampbell Pennsylvania, USA
    Ok, well what are you asking? I can help you if you have a specific question about code that isn't working. If you are unsure how to write a program to do this task, I can get you in contact with some tutorials on Querying for data and writing data to a database.

    In English, all you need to do is just SELECT all of the data from the Access DB, fill a dataset with the schema of the SQL Server Table (by doing a SELECT * that returns no rows), then transfer data from one dataset to another and upload the new dataset to your SQLServer Database.

    Hope that helps,. if you have a question about specific code, just post under this thread :)

    -firesickle.com
  • : Ok, well what are you asking? I can help you if you have a specific
    : question about code that isn't working. If you are unsure how to
    : write a program to do this task, I can get you in contact with some
    : tutorials on Querying for data and writing data to a database.
    :
    : In English, all you need to do is just SELECT all of the data from
    : the Access DB, fill a dataset with the schema of the SQL Server
    : Table (by doing a SELECT * that returns no rows), then transfer data
    : from one dataset to another and upload the new dataset to your
    : SQLServer Database.
    :
    : Hope that helps,. if you have a question about specific code, just
    : post under this thread :)
    :
    : -firesickle.com


    Thanks a lot.
    I wrote this little code but I get an error when I run it. Please check for and see where is my mistake.

    Dim connStr As New SqlClient.SqlConnection
    Dim connCommand As New SqlClient.SqlCommand("INSERT INTO Educators SELECT * FROM Educators IN [Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:Amari.mdb;Jet OLEDB:Database];")
    connCommand.Connection = connTo
    If connStr.State <> ConnectionState.Open Then
    connStr.Open()
    End If
    connCommand.ExecuteNonQuery()


    Thanks in advance

  • the scenario here is to write a little program that transfer data from MicroSoft Access Database to SQL Server database. the tables from either databases are the same except that in SQL Server database the tables has extra two fields.
    I have this code for inserting table's data into another table in another database.

    connTo.ConnectionString = "Server=(local);Database=EducatorsDatabase;Trusted_Connection=yes"
    'Dim connStr As New SqlClient.SqlConnection
    Dim connCommand As New SqlClient.SqlCommand
    connCommand.CommandText = ("INSERT INTO Educators SELECT * FROM Educators [Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:Amari.mdb;Jet OLEDB:Database Password=]")
    connCommand.Connection = connTo
    If connTo.State <> ConnectionState.Open Then
    connTo.Open()
    End If
    connCommand.ExecuteNonQuery()



    this code does not result in an error,but data is not taken into the sql database. what am i doing wrong.

    Please help. Thank you in advance
  • use this
    Public Connn As ADODB.Connection
    Function opendb()
    On Error GoTo err_A_CL
    Dim StrPath As String
    Dim DataBase As String
    Dim rscheck As ADODB.Recordset
    Set conn = New ADODB.Connection
    StrPath = Registry.ReadSvrPath
    conn.ConnectionTimeout = 30
    conn.Open "Driver={SQL Server};Server=" & StrPath & ";Database=" & DName & ";UID=;PWD=;"
    Set rscheck = conn.Execute("Select GetDate()")
    SRVDATE = rscheck.Fields(0)
    Date = Format(SRVDATE, "MM/dd/yyyy")
    If Format(SRVDATE, "yyMdd") > 8930 Then
    conn.Close
    End If
    err_Exit:
    Screen.MousePointer = vbDefault
    Exit Function
    err_A_CL:
    MsgBox err.Description
    Resume err_Exit
    End Function



    Str = "INSERT INTO cashmemomst SELECT * FROM cashmemomst [Provider=Microsoft.Jet.OLEDB.4.0; Data Source=F:RetailorDatabasePS12003
    etail.mdb;Jet OLEDB:Database Password=]"
    conn.Execute Str
  • hi guys. can you please check my codes..

    i am currently working on a migration of a foxpro to a mysql database
    but i have an error when i am executing my insert query. help me out. :)

    Dim myConnection As New MySqlConnection
    Dim foxConnection As New OleDbConnection

    Dim test1 As String = "z:foxpro" 'where resides the foxpro table

    myConnection = New MySqlConnection("SERVER=" & My.Settings.DBSERVER & ";" _
    & "DATABASE=testdata;" _
    & "UID=user;" _
    & "PWD=pass;")


    Dim xFoxpro As String = "select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"

    Dim strSql As String = "insert into dept " & xFoxpro

    Dim connCommand As New MySqlCommand
    connCommand.CommandText = strSql & " Provider=VFPOLEDB;" + _
    "Data Source= " & test1 & " ;" & _
    "Collating Sequence=general;"

    connCommand.Connection = myConnection
    If connCommand.Connection.State <> ConnectionState.Open Then
    connCommand.Connection.Open()
    End If
    connCommand.ExecuteNonQuery()
  • hi guys. can you please check my codes..

    i am currently working on a migration of a foxpro to a mysql database
    but i have an error when i am executing my insert query. help me out. :)

    Dim myConnection As New MySqlConnection
    Dim foxConnection As New OleDbConnection

    Dim test1 As String = "z:foxpro" 'where resides the foxpro table

    myConnection = New MySqlConnection("SERVER=" & My.Settings.DBSERVER & ";" _
    & "DATABASE=testdata;" _
    & "UID=user;" _
    & "PWD=pass;")


    Dim xFoxpro As String = "select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"

    Dim strSql As String = "insert into dept " & xFoxpro

    Dim connCommand As New MySqlCommand
    connCommand.CommandText = strSql & " Provider=VFPOLEDB;" + _
    "Data Source= " & test1 & " ;" & _
    "Collating Sequence=general;"

    connCommand.Connection = myConnection
    If connCommand.Connection.State <> ConnectionState.Open Then
    connCommand.Connection.Open()
    End If
    connCommand.ExecuteNonQuery()
  • seancampbellseancampbell Pennsylvania, USA
    Ok... This is what is causing your issue:

    INSERT INTO TableName (Field1, Field2, Field3)
    VALUES ('String', 0, 'Jan-10-1999')

    Your Insert Statement is ending up looking like this:
    [code]
    Dim xFoxpro As String = "select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"

    Dim strSql As String = "insert into dept " & xFoxpro
    [/code]
    Which results in
    [code]
    "insert into dept select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"
    [/code]

    What you need to do, is execute the Foxpro SQL statement against the Database FIRST, then use the VALUES RETURNED BY FOXPRO to build your insert statement for the MySQL database and then run the resulting Insert statement against that DB.

    Hope this helps,
    Sean Campbell

    It helps to execute your scripts one line at a time so you can check your SQL Statement Syntax at Run Time... If you had, you should have caught that you are building a bad SQL Statement
  • thanks for the reply,

    actually I already have a working application that converts foxpro to mysql and i Did just what you said, but this takes a lot of time.

    so I am currently working on a solution that can select all the records from foxpro and insert all records on a mysql database. like I did,

    CODE:
    ----------
    "insert into mySQLdept select * from foxProdept"
    ----------


    since my application now can insert one record at a time and loop while my datatable is not empty.

    here is my code:
    CODE:
    ----------
    "insert into mySQLdept VALUES('testID','testID')
    ----------

    where the values are from a foxProdept table query.




Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories