How to Import Data from Ms access table to sql server table

Question

I have one table, Checkinout in Ms Access, and I want to import all the data of that table to my SQL Server database Checkinout table, using VB 6.0:

This is my code. It is not working perfectly [is this irony?]

   For i = 1 To LstLog.ListItems.Count
       For j = 1 To LstLog.ColumnHeaders.Count - 1
           REC.Open "insert into xyz (EmpID, LogID,CheckTime,SensorID) Values ('" & _
               LstLog.ListItems(i).SubItems(j) & "','" & _
               LstLog.ListItems(i).SubItems(j) & "','" & _
               LstLog.ListItems(i).SubItems(j) & "','" & _
               LstLog.ListItems(i).SubItems(j) & "' )", CN, _
               adOpenStatic, adLockBatchOptimistic
       Next j
   Next i

I think you can create ODBC connections to each database and then use them in your program to transfer data from one to the other... But I must ask, what have you tried so far? — Barranka
– Barranka, Commented Apr 24, 2013 at 5:18

Freelancer · Accepted Answer · 2013-04-24 05:31:20Z

1

You can use DBConvert for this purpose.

DBConvert for Access and MySQL migration tool converts Microsoft Access to MySQL server and MySQL to Access.

You can refer this direct link for referance:

http://dbconvert.com/convert-access-to-mysql-pro.php?DB=1

If you want to do it programatically,

Step By Step Approach:

http://en.kioskea.net/faq/7342-export-access-database-to-mysql

answered Apr 24, 2013 at 5:31

Freelancer

9,1027 gold badges46 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2273064 Over a year ago

For i = 1 To LstLog.ListItems.Count For j = 1 To LstLog.ColumnHeaders.Count - 1 REC.Open "insert into xyz (EmpID, LogID,CheckTime,SensorID) Values ('" & LstLog.ListItems(i).SubItems(j) & "','" & LstLog.ListItems(i).SubItems(j) & "','" & LstLog.ListItems(i).SubItems(j) & "','" & LstLog.ListItems(i).SubItems(j) & "' )", CN, adOpenStatic, adLockBatchOptimistic Next j Next i CN.Close End If

Mark Bertenshaw · Accepted Answer · 2013-04-24 15:38:38Z

0

You have used the same sub item id. Drop the inner for loop, e.g.:

For i = 1 To LstLog.ListItems.Count
    REC.Open "insert into xyz (EmpID, LogID,CheckTime,SensorID) Values ('" & _
        LstLog.ListItems(i).SubItems(1) & "','" & _
        LstLog.ListItems(i).SubItems(2) & "','" & _
        LstLog.ListItems(i).SubItems(3) & "','" & _
        LstLog.ListItems(i).SubItems(4) & "' )", CN, _
        adOpenStatic, adLockBatchOptimistic
Next i

answered Apr 24, 2013 at 15:38

Mark Bertenshaw

5,7093 gold badges30 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to Import Data from Ms access table to sql server table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related