Save DataTable to Access

[Sorwen]

I have a DataTable that I filled from another source. I then need to save this DataTable to an Access Database. Nothing I've tried so far has worked. I've been trying to do this with an OleDbDataAdapter. The last attempt I pulled it in as a ADODB Recordset and then tried to create it:

Dim Conn As New ADODB.Connection
        Dim rsTable As New ADODB.Recordset
        Dim strSQL As String

        Try
            With Conn
                .ConnectionString = ProviderData
                .Open()
            End With

            strSQL = ""
            strSQL = strSQL & "Select *"
            strSQL = strSQL & " From ODBC.RemoteTableName"

            rsTable.Open(strSQL, Conn, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic)
            rsTable.Close()

            Return rsTable
        Catch ex As Exception
            MsgBox(ex.Message)
            Return ex.Message
        End Try

        strsql = "Create Table large_tbl (Aonrvo Text(50), Lmpd Text(50))"

        Dim conn2 As New OleDbConnection(ProviderData)
        Dim da As New OleDbAdapter(strsql, conn2)
        Dim cds As New DataSet

        da.Fill(cds, rsTable, "large_tbl")
        da.Update(cds, "large_tbl")

I didn't really think it would work, but everything else I tried didn't either so what-the-heck.

-I hate Microsoft!
-Forever and always forward.

 

[Sorwen]

oops forgot to remove the Return rsTable. It isn't in the current code I'm running. The original was in its own function.

-I hate Microsoft!
-Forever and always forward.

 

[AlexCuse]

I don't see a DataTable anywhere but in your post title, and I can't really make much sense of the code you've posted.

I've never used a recordset in .net, but in AFAIK the only way to get a DataTable into a non-SQL Server database is to build insert commands one row at a time and execute them. I have some code at home that does this if that's what you're after.

However, if you are bringing your data in from access, and modifying it, perhaps the DataAdapter's UPDATE method would be more to your liking?

http://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.update(vs.80).aspx

I think there is also a way to create a DataSet from the table you want to insert to, merge it with your DataTable's DataSet, and then update, but it seems since this is going to be doing the updates/inserts row by row anyways it might be better to just issue the insert commands yourself.

If you could say where the DataTable/Recordset is coming from it would be easier to tell how you should proceed.

Hope this helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[Sorwen]

Thanks for the help. Update doesn't work neither the way I tried it or with a merge. The data merges and the dataset updates, but the access table does not. Yes, there isn't a DataTable in the code above because I couldn't get it to work so I tried the code above (which didn't work either). I mentioned the DataTable in the hope someone knew how to do it that way instead of the way I tried above.

The code above pulls data from an odbc source into a record set then fills a Dataset (from that Recordset) with a table called "large_tbl". This works, but the dataset which should be linked to the Access database doesn't update the database. As a quick fix right now I do it row by row, but on larger data that tends to be way to slow. Thanks again for trying to help.

-I hate Microsoft!
-Forever and always forward.

 

[AlexCuse]

I'm pretty sure you will have to do it row by row. You may be able to wrap your inserts in a transaction (I know you can from vba) to help the speed a little. Also make sure you're not opening the connection for each individual row insert. I'm currently doing something like this for a project I'm working on, I will see this weekend if an OleDbTransaction will work on Access.

If there is another way I would be very interested to know, so please post back if you find anything.

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[PRPhx]

Sorwen,
Have you tried doing an INSERT INTO your Access table???? That should work.

 

[Sorwen]

I think I tried it, but wasn't sure so just did so and INSERT INTO doesn't work either. Thanks for the help.

-I hate Microsoft!
-Forever and always forward.

 

[PRPhx]

What, if any, error messages do you get when you try to do an insert???

Does the user have permission to write/modify the folder where your Access db resides?

Also, realize that when you try to insert data from one db to another you are likely to run into data conversion problems. I find this to be esp. true when I am trying to import dates, even from one Microsoft product to another.

Excel spreadsheet imports can cause lots of headaches to, esp. if you convert from numeric to string(char) data.

 

[Sorwen]

None.

Yes. I can write to the table in any other manner I like as long as I don't try to do it with a dataadapter/dataset with an sql statement.

Nope handled. The destination table is preset to the correct data types and they are unchanging.

I really don't want to, but I guess I'm going to have to take it all back to ADODB so I can write it how I want. That will accept a create/insert into/etc. The only problem is I don't know how to merge tables from two different connections in vb.net(I'm use to working in access with it). The other problem with that is most of the tools I've created work with adapters/datatables/datasets now.

Thanks for the help.

-I hate Microsoft!
-Forever and always forward.

 

[AlexCuse]

You never mentioned what the ODBC Source for your data is. If you did, you would find that there might be a way to link the source directly to access (so it is visible as if it were a table to the jet engine), then it becomes as simple as issuing a SELECT INTO query.

There also could be a way to make the 'ODBC Source' aware of your access DB.

I think you'd want to avoid using ADODB if at all possible, especially if your only reason is

so I can write it how I want

Good Luck,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[Sorwen]

I can't allow the database to link to the data directly. There several reasons, but the biggest is that while I have access to both locations they do not have access to each other. There is no way to make the source aware of the database. No that no one is capable, but that the software is not.

Thank you for the help and suggestions.

-I hate Microsoft!
-Forever and always forward.