MS Excel connection with vb.net

0
votes

I have used the connection string below but I am getting an error when trying to create a table

Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFName + _ ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""

Cannot modify the design of table 'tablename'. It is in a read-only database.

4
vb.netexcel
I think it would help if you provided some more information, maybe supply some more of the relevant code. – Phaedrus
Or maybe the SQL that you're trying to run – Ant
and supply us with the exact error – K_B

4 Answers

1
votes

If the database is read-only, then by definition you will not be able to create any tables in it.

1
votes

Make absolutely certain that you do have write access to the file. For example are you accessing this from IIS which only has limited permissions. Check the security of the directory. Try a normal File.Open() of the file in the same process.

0
votes

I'm personally using the following to connect to an access database:

    _source = "..\db.mdb"
    Dim strconnexion As String
    strconnexion = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strconnexion &= "User ID=Admin;Password=;"
    strconnexion &= "Data source=" & _source
    _cnBd = New OleDbConnection (strconnexion)
    _cnBd.Open()

Hope this helps.

0
votes

Your problem is the IMEX=1. That tells excel to open in "import mode" making the connection read-only. I had the same issue, weird weird stuff.

Take that out and it works like a charm.