0

how to transfer data from excel to ms access database?? and how to retrieve data from ms access database using visual basic 6.0??anybody can help me..

3
Contributors
3
Replies
30
Views
3 Years
Discussion Span
Last Post by ling_tj
0

first of all you have to add many components
-Ms ADO Data Control 6.0
-Ms Common Dialog 6.0

on A1, B1, C1, ... (Row Excel) fill with name of atributes on table as like as Access.
for example ID, Name
start the data that you what to save in A2, B2, ...

this is the code:

Dim db As ADODB.Connection

CommonDialog1.InitDir = ""
CommonDialog1.DialogTitle = "Select the Excel File to import from"
CommonDialog1.Filter = "Excel(*.xls)|*.xls"
CommonDialog1.ShowOpen

If CommonDialog1.FileName <> "" Then
    Set db = New ADODB.Connection`
    db.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & App.Path & "\testA.mdb;Persist Security Info=False"
    db.Open
    sqlstring = "INSERT INTO test SELECT * FROM [Excel 8.0;HDR=YES;Database=testE.xls].[Sheet1$]"
Else
    MsgBox "File not exists"
End If

CommonDialog1.FileName = ""
db.Execute sqlstring
MsgBox "Successfully Imported"
db.Close
Set db = Nothing

note:
testA is the name of database Access
testE is name of Excel file
test is table name.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.