Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004

    Unanswered: how to export Access mdb to Sql database....

    I have a mdb in Access named test1.mdb with tabale named test_table and a Db Sql test2.mdf with tabale named test_table.
    Each table of database have the same name fileds: prova1, prova2, prova3...prova20.
    Now with vba or vb classic is possible to transfer the data from Access .mdb into Sql table, Record by Record


    prova1 from .Mdb to Prova1 slq table
    prova2 from .Mdb to Prova2 slq table
    prova3 from .Mdb to Prova3 slq table
    prova20 from .Mdb to Prova20 slq table

    Tks to all.
    I use this code to pass dta with excel and inseted Excel i would want to use access mbd to sql table...
    In effect import into sql databse record by record from access mdb....

    Sub ADOExcelSQLServer()

    Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset
    Dim RowNo As Long

    Set rs = New ADODB.Recordset

    Server_Name = "USER-E114319F02"
    Database_Name = "northwind"
    User_ID = ""
    Password = ""
    SQLStr = "SELECT * FROM customers"

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic
    RowNo = 2

    Do While Not rs.EOF
    Range("a" & RowNo).Value = rs(1)
    Range("b" & RowNo).Value = rs(2)
    Range("c" & RowNo).Value = rs(3)
    RowNo = RowNo + 1

    Set rs = Nothing
    Set Cn = Nothing
    End Sub

  2. #2
    Join Date
    Mar 2005
    Kiev, Ukraine
    Why not just connect to your Access database from SQL DTS? It would allow you to transfer all data.

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    Use DTS, or simply link your SQL table into Access.
    Whatever method you use, if you find yourself looping through records doing individual inserts, you are definitely doing something wrong.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts