vb.net MS Access inserting rows from one db to another

Question

I'm trying to import rows from one db to another, basically it something to do with this SQL:

SELECT * INTO [MSAccess;DATABASE=C:\MainDB.mdb;].[Header]  FROM [Header] WHERE ID=9

As it returns this error: Could not find installable ISAM.

Any ideas? To help explain I've added my code:

Dim sSQL As String
Dim iCertMainNo As Integer
Dim cnLocal As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & App_Path() & "LocalDB.mdb;")
Dim cnMain As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & My.Settings.MainDB & ";")

cnLocal.Open()
cnMain.Open()

Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * INTO [MSAccess;DATABASE=" & My.Settings.MainDB & ";].[tblCertHeader] FROM tblCertHeader WHERE ID = " & iCertNo, cnLocal)
cmd.ExecuteNonQuery()



cnMain.Close()
cnLocal.Close()

I'm thinking it's either do it the way listed above. Or to open two connections get one row from the local and then insert it into cnMain - but again not sure how to do this without listing all the fields... Can I just simply insert the row ?

Fionnuala · Accepted Answer · 2011-09-19 14:07:52Z

1

it appears you are running from one MS Access database to another, so the connect string is much simpler:

SELECT * INTO [;DATABASE=C:\MainDB.mdb;].[Header]  FROM [Header] WHERE ID=9

BTW It may not be possible to update a database in C:\, if that is a real path.

EDIT I tested with this:

''Dim sSQL As String
''Dim iCertMainNo As Integer
Dim cnLocal As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Docs\dbFrom.mdb;")
''Dim cnMain As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & My.Settings.MainDB & ";")

cnLocal.Open()
''cnMain.Open()

Dim cmd As New System.Data.OleDb.OleDbCommand("SELECT * INTO [;DATABASE=C:\Docs\DBTo.mdb;].[Header] FROM Header WHERE ID = 2", cnLocal)
cmd.ExecuteNonQuery()



''cnMain.Close()
cnLocal.Close()

And it worked fine for me. I commented out iCertMainNo because you did not use it. Your string included only iCertNo, for which i used the actual value for test purposes. I did not see any reason for two connections.

edited Sep 19, 2011 at 14:07

answered Sep 19, 2011 at 10:30

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

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

7 Comments

Shane Over a year ago

It's not the actual path - but why say that? Why wouldn't it be able to update there?

Fionnuala Over a year ago

Because for one, Access creates a lock file and permissions on root will prevent that.

Shane Over a year ago

Well it's not that... Anybody got any real solutions ?

Fionnuala Over a year ago

@Shane Perhaps you would care to elaborate? A little code context might also be a good idea.

Shane Over a year ago

Changed my question, does that help?

|

Collectives™ on Stack Overflow

vb.net MS Access inserting rows from one db to another

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related