Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to export Access mdb to Sql database.... (2000)

    ...
    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    Your post is confusing - Prova1 etc. seem to be fields in a table first, and tables later.

    You can create links to the SQL Server tables in the Access database and create append queries that append records from the Access tables to the SQL Server tables. If you wish, you can run these queries in VBA code using DoCmd.OpenQuery or CurrentDb.Execute.

    Note: SQL Server has a powerful tool DTS (Data Transformation Services) that can be used to import data into SQL Server and to export data from it - it might be useful to investigate that.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    Since the Access and SQL Server tables have the same structure, you don't have to transfer record by record. It's MUCH faster to execute an append query.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    sure.. but during export from acees to sql database a would want make other operation on a signle record..

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    For example?

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    My friend, after tree day i have maked this code. It export cells value into sql database.
    Now insted to use sheet, column and cells is possible to arrange the code to use table, fields and record from an mdb database.?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    See <post:=581,096>post 581,096</post:>.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    See Now...:-) ...

    But 2 question:
    1) wath is the correct dimension of: Dim rsAccess
    2) why the record on sql table not is updating?
    i have always the same record in sql table....

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    1) Dim rsAccess As ADODB.Recordset

    2) Move the line

    rsAccess.MoveNext

    down to immediately above the line with Loop.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    Sorry Hans but wath is the difference from:

    UpdateBatch
    and
    Update

    is the same or have a different option...
    Tks.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    Update is used to update a single record at a time, and UpdateBatch to update a series of records in one go. Since you update only one record at a time, you should use Update.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to export Access mdb to Sql database.... (2000)

    tks!

Posting Permissions

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