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

    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

    Example:

    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.
    note:
    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
    rs.MoveFirst

    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
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    End Sub

  2. #2
    Join Date
    Mar 2005
    Location
    Kiev, Ukraine
    Posts
    5
    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
    Location
    Ohio
    Posts
    12,592
    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
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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