CODEWITHSUNDEEP
vb.netexcelms-accessimportexport
LUT-V D' ANGEL
LUT-V D' ANGEL

Reputation: 11

Import Excel data to Access database

I want to import data from Excel to Access like this, it's working when my Access database has no password, but not working when it has a password. I copy this code from internet.

Where do I have to put the password of Access?

Dim Access As String = "C:\FWS\Database1.accdb"
Dim Excel As String = "C:\New folder\2h.xlsx"
' Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & ";Extended Properties=Excel 8.0;"

Dim connect As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel + ";Extended Properties=""Excel 12.0 Xml;HRD=NO"""


    Using conn As New OleDbConnection(connect)
        Using cmd As New OleDbCommand()
            cmd.Connection = conn
            cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[Password=frozbit].[tb_voucher2] SELECT * FROM [2hari$]"
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using

Upvotes: 1

Views: 5935

Answers (3)

user8612838
user8612838

Reputation: 1

please use your db password like my code.my password is 123.

    Using cmd As New OleDbCommand()
    cmd.Connection = conn
    cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & ";PWD=**123**].[Sheet1] SELECT * FROM [Sheet1$]"
    If conn.State = ConnectionState.Open Then
       conn.Close()
    End If
    conn.Open()
    cmd.ExecuteNonQuery()
    MessageBox.Show("succussfully updated")
    Form4.Show()
    Me.Hide()

Upvotes: 0

Ahmed
Ahmed

Reputation: 11

Change the cmd.commandText to be like this:

cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & ";
    PWD=frozbit].[tb_voucher2] SELECT * FROM [2hari$]"

Upvotes: 1

Alex Hedley
Alex Hedley

Reputation: 786

From a quick search:

https://www.connectionstrings.com/access/

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:Database Password=MyDbPassword;"

http://www.sqlstrings.com/ms-access-connection-strings.htm

"Driver= {MicrosoftAccessDriver(*.mdb)}; DBQ=C:\App1\Your_Database_Name.mdb;Uid=Your_Username; Pwd=Your_Password;"

Or

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; User Id=admin; Password="

NOTE: These are untested.

Upvotes: 0

Related Questions