f



copying data from MS-SQL to MS-Access using VB.Net

Hi there,

I'm trying to use MSAccess as a "container" to move data around from
one MS-SQL server DB to another. This is basically already a design
decision that has been made for a lot of reasons and can't be changed
by me.

What I was wondering is what is the bet way to populate the MS-Access
DB. The DB will only contain 6 tables which will have a very small
amount of data in them. I have tried to use ADO.Net to read the data
from MS-SQL into a DataTable and then save it into the MS-Access DB
using again using a DataTable populated from the first, but I can't
seem to get the syntax right.

Even after I execute the Update method nothing happens to the Access
DB. So I modified one of the values in the data rows ('myRow("name") =
"hi there") and this seemed to force an update, but then threw a
concurrency error.

Attached below is the code I was using, can anyone help?

JPO




        Dim cnnA As System.Data.OLEdb.OleDbConnection = New
System.Data.OLEdb.OleDbConnection(QuantumConnection)
        Dim cnnAccessDB As System.Data.OLEdb.OleDbConnection = New
System.Data.OLEdb.OleDbConnection(AccessDB)

        cnnA.Open()
        cnnAccessDB.Open()

        Dim daDataAdapterA As New
System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Advmast", cnnA),
dtDataTableA As New DataTable
        Dim daAccessDB As New
System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Advmast",
cnnAccessDB), dtAccessDB As New DataTable
        
        Dim myRow As DataRow
        Dim i As Int16

        daDataAdapterA.Fill(dtDataTableA)

        dtAccessDB = dtDataTableA.Clone
        dtAccessDB.BeginLoadData()
        For Each myRow In dtDataTableA.Rows
            'myRow("name") = "hi there"
            dtAccessDB.ImportRow(myRow)
            daAccessDB.Update(dtAccessDB)
        Next myRow
        dtAccessDB.EndLoadData()

        daAccessDB.Update(dtAccessDB)
        dtAccessDB.AcceptChanges()

        cnnAccessDB.Close()
        cnnA.Close()
        cnnAccessDB = Nothing
        cnnA = Nothing
0
3/30/2005 6:46:32 AM
comp.lang.basic.visual.misc 10153 articles. 0 followers. Post Follow

1 Replies
972 Views

Similar Articles

[PageSpeed] 30

Almost everybody in this newsgroup is using VB6 or lower. While you may get
a stray answer to VB.NET questions here, you should ask them in newsgroups
devoted exclusively to .NET programming. Look for newsgroups with either the
word "dotnet" or "vsnet" in their name.

For the microsoft news server, try these newsgroups...

microsoft.public.dotnet.general
microsoft.public.dotnet.languages.vb
microsoft.public.vsnet.general

-- 
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
--
Read. Decide. Sign the petition to Microsoft.
http://classicvb.org/petition/


"JPO" <justin.parry-okeden@sungard.com> wrote in message
news:a3311a11.0503292246.45487234@posting.google.com...
> Hi there,
>
> I'm trying to use MSAccess as a "container" to move data around from
> one MS-SQL server DB to another. This is basically already a design
> decision that has been made for a lot of reasons and can't be changed
> by me.
>
> What I was wondering is what is the bet way to populate the MS-Access
> DB. The DB will only contain 6 tables which will have a very small
> amount of data in them. I have tried to use ADO.Net to read the data
> from MS-SQL into a DataTable and then save it into the MS-Access DB
> using again using a DataTable populated from the first, but I can't
> seem to get the syntax right.
>
> Even after I execute the Update method nothing happens to the Access
> DB. So I modified one of the values in the data rows ('myRow("name") =
> "hi there") and this seemed to force an update, but then threw a
> concurrency error.
>
> Attached below is the code I was using, can anyone help?
>
> JPO
>
>
>
>
>         Dim cnnA As System.Data.OLEdb.OleDbConnection = New
> System.Data.OLEdb.OleDbConnection(QuantumConnection)
>         Dim cnnAccessDB As System.Data.OLEdb.OleDbConnection = New
> System.Data.OLEdb.OleDbConnection(AccessDB)
>
>         cnnA.Open()
>         cnnAccessDB.Open()
>
>         Dim daDataAdapterA As New
> System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Advmast", cnnA),
> dtDataTableA As New DataTable
>         Dim daAccessDB As New
> System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Advmast",
> cnnAccessDB), dtAccessDB As New DataTable
>
>         Dim myRow As DataRow
>         Dim i As Int16
>
>         daDataAdapterA.Fill(dtDataTableA)
>
>         dtAccessDB = dtDataTableA.Clone
>         dtAccessDB.BeginLoadData()
>         For Each myRow In dtDataTableA.Rows
>             'myRow("name") = "hi there"
>             dtAccessDB.ImportRow(myRow)
>             daAccessDB.Update(dtAccessDB)
>         Next myRow
>         dtAccessDB.EndLoadData()
>
>         daAccessDB.Update(dtAccessDB)
>         dtAccessDB.AcceptChanges()
>
>         cnnAccessDB.Close()
>         cnnA.Close()
>         cnnAccessDB = Nothing
>         cnnA = Nothing


0
3/30/2005 5:19:46 PM
Reply:

Similar Artilces:

Using GetOleDbSchemaTable and Visual Basic .NET for MS-Access and SQL Server Express
I need to write a VB.Net application that will take an mdb file (Access 2000) and create corresponding copy of it in SQL Express, creating all the tables and relationships, and transferring over the data (using an existing application like DTS in SQL Server or something else is NOT an option - this must be coded from scratch). The Access databases that it must be able to handle will only have tables, relationships, and data (i.e. no forms, macros, queries, reports). I see that in the System.Data.OleDb namespace there is the method GetOleDbSchemaTable that returns all sorts of schema information. I haven't fully explored how to use this method, but do you think I will be able to pull out all that I need from the various schema information returned to rebuilt the mdb file in SQL Express? I just don't want to start heading down this road and invest quite a bit of effort only to hit an impassable road block. Thank you for any thoughts on this! Marcus I don't know of any gotchas with using GetOleDbSchemaTable to roll your own upsizing tool. However, you'll need to come up with your own Access to SQL Server data type mappings. -- Hope this helps. Dan Guzman SQL Server MVP "Marcus" <holysmokes99@hotmail.com> wrote in message news:1139699982.671856.64020@g44g2000cwa.googlegroups.com... >I need to write a VB.Net application that will take an mdb file (Access > 2000) and create corresponding copy of it in SQL Express, creating all >...

Possible to keep MS Access interface and migrate the MS Access to MS SQL Server?
Hello all Would it be possible to migrate the MS Access 2000 to MS SQL Server 2000? My application is using MS Access 2000 as database and as user interface such as forms. Now, I want to migrate the backend database from MS Access 2000 to MS SQL Server 2000. However, I want to keep the MS Access 2000 interface. Would it be possible? If I migrate the MS Access to SQL Server, would the queries, back-end VBA, macro, tables and forms be affected? Do I need to change the MS Access data type to SQL server supported data type? Which tool I can use to do the migration? Upsizing wizard or exporting the Access database and then importing it to the SQL server? Thanks in advance Cheers Bon This is somewhat easy. Basically you need to port or transfer your Access tables to a SQL DB and then link the Access DB/App. to the SQL tables on the SQL Server. If you keep the table names the same, the code etc shouldn't need altering, bu I would test it first. db55 wrote: > This is somewhat easy. > > Basically you need to port or transfer your Access tables to a SQL DB > and then link the Access DB/App. to the SQL tables on the SQL Server. > If you keep the table names the same, the code etc shouldn't need > altering, bu I would test it first. > If only it were that easy :-) Bon wrote: > Hello all > > Would it be possible to migrate the MS Access 2000 to MS SQL Server > 2000? > > My application is using MS Access 2000 as database and a...

US-TX-Austin: Web Programmer, MS Visual Studio.NET, .NET Framework, Access, SQL; (45338452258)
US-TX-Austin: Web Programmer, MS Visual Studio.NET, .NET Framework, Access, SQL; (45338452258) ============================================================================================== Position: Web Programmer Reference: SMC01937 Location: Austin TX Duration: 6M Skills: Recent work experience MS Visual Studio�. NET and the MS .NET Framework. 2+yrs exp performing ongoing support and testing of database systems using Microsoft Access and SQL. 2+yrs exp supporting Office 2000/2003 (Word, Access, Excel ...

US-TX-Austin: Web Programmer, MS Visual Studio.NET, .NET Framework, Access, SQL; (45338457612)
US-TX-Austin: Web Programmer, MS Visual Studio.NET, .NET Framework, Access, SQL; (45338457612) ============================================================================================== Position: Web Programmer Reference: SMC01937 Location: Austin TX Duration: 6M Skills: Recent work experience MS Visual Studio�. NET and the MS .NET Framework. 2+yrs exp performing ongoing support and testing of database systems using Microsoft Access and SQL. 2+yrs exp supporting Office 2000/2003 (Word, Access, Excel ...

How to access data from an MS Excel sheet and import it to a MS Word using LabView?
Hello I would like to know how is it possible to access a specified data( for example row 1 ,column 2 to 5) in an Excel sheet and import it to a word document using LabView. Thanks You can interact with Office application through ActiveX, but you should be warned that it requires opening a lot of properties and going deep into the Office object hierarchy and this does not look nice in LabVIEW. If you open the example finder (Help&gt;&gt;Find Examples) and search for "ActiveX" or "Excel" you should find some examples on the basics of working with Excel. You can do the same with Word, so you can get your data from Excel and put it into Word. If you search here you should also find some threads dealing with this. To learn more about LabVIEW, I suggest you try searching this site and google for LabVIEW tutorials. <a href="http://cnx.rice.edu/content/col10241/latest/" target="_blank">Here</a>, <a href="http://zone.ni.com/devzone/learningcenter.nsf/03f7c60f17aad210862567a90054a26c/55974411828f779086256ce9007504bd" target="_blank">here</a>, <a href="http://www.mech.uwa.edu.au/jpt/tutorial/index.html" target="_blank">here</a>, <a href="http://www.iit.edu/~labview/Dummies.html" target="_blank">here</a> and <a href="http://www.upscale.utoronto.ca/GeneralInterest/LabView.html" target="_blank">here</a...

Moving data from MS SQL Server 2000 or MS Access XP to UniData 5.2.9
Complete MVDBMS neophyte -I may not even qualify at the neophyte level- that needs a simple solution to move data -in batch initially...real-time eventually- from RDBMSs such as SQL Server 2000 or Access 2002 (XP) to UniData 5.2.9. Any leads would be greatly appreciated. --Ryan N. Ryan N. wrote: > Complete MVDBMS neophyte -I may not even qualify at the neophyte > level- that needs a simple solution to move data -in batch > initially...real-time eventually- from RDBMSs such as SQL Server 2000 > or Access 2002 (XP) to UniData 5.2.9. > > Any leads would be greatly appreciated. > > --Ryan N. Accuterm, look at the Excel option, and then the scripting to move it on. www.asent.com Better yet, most likely you need a consultant. Check out Tony Gravagno. He's good at building interfaces, and has most of the tools pre-built. He is at http:(no spam)//nebula-rnd.com/ get rid of the (no spam) HTH, Patrick <;=) "(Latimerp)" <"(Latimerp)"@comcast.net> wrote: >Ryan N. wrote: >> Complete MVDBMS neophyte -I may not even qualify at the neophyte >> level- that needs a simple solution to move data -in batch >> initially...real-time eventually- from RDBMSs such as SQL Server 2000 >> or Access 2002 (XP) to UniData 5.2.9. >> >> Any leads would be greatly appreciated. >> >> --Ryan N. > >Accuterm, look at the Excel option, and then t...

How can i use tclodbc to access the Ms sql 2000,and get data from it?
Can somebody give me the script? I will be appreciated for it. Q2:Can We use tclodbc to access the Oracle database? And how? ...

MS Access conversion to MS SQL
Hi, I've been developing a database for a department in a local organisation. The database is in Microsoft Access and uses tables, queries, forms and reports. Written into the forms is vast amounts of visual basic. There are also a couple of macros. In the time I've been developing this database (a year now, as I went back to university, and then finished university and returned to finish off the DB) the organisation I've been working for have decided to start using MS SQL. The department I am working for aren't to happy as not only my database but most of theirs are in Access and no one seems to have actually used SQL or know anything much about it. Basically, I need to learn about how to convert from Access to SQL. I understand we use the upsizing wizard. I've managed to track down some of the rules I have to use in the conversion (like no spaces in names, no apostrophes etc), but I'd like to know if anyone could tell me more about the upsizing procedure. I'm also concerned about the vast amounts of forms I've made (It's a 109mb database with no data - all down to the forms!). What happens to these? Does SQL use forms, or will all of these be lost? And what about queries? Any information from someone who has knowledge of these things would be greatly appreciated! Many thanks, Stu. Hiya, An account of how I did it can be found at: http://www.cooldigitec.co.uk/NJW/15-AccessToSQLServer7.html Basically, 1. Close all database objects...

Using ".OpenCurrentDatabase" in VB .net to open an MS Access DB
Hello, when I execute the code below I get the usual access message: SECURITY WARNING---------------------------------- Opening "<filepath & name.mdb>" The file may not be safe if it contains code that was intended to harm your computer. Do you want to cancel the operation? CANCEL OPEN MORE INFO --------------------------------------------------- Private Sub OpenDB(ByVal strMDBFullPath as string) Dim appDatabase As Access.Application appDatabase = New Access.Application() appDatabase.OpenCurrentDatabase(p_strMDBFullPath, True) .... End Sub How can I detect if the user presses the CANCEl button? With the code above if the user presses the CANCEl button the code throws an exception on the line "appDatabase.OpenCurrentDatabase(p_strMDBFullPath, True)". The code is VB .net and the database is 2003 but could be 97, 2000 etc. Thanks in advance Paul M. No to answer your question, but: do you really want to open that database /in the Access user interface/? Or do you just want to open it "behind the scenes", so you can get at the data within it? If the latter, then, you need OpenDatabase - not OpenCurrentDatabase. HTH, TC [MVP Access] I wonder what you are trying to do do you want to retrieve data from the access database ? if so this is not the way ( you propbaly have set a reference to the office lib ) normally you access a access database by the oledb namespace regards Michel Posseth [MCP] "Paul M&...

Opening MLM (Groupwise emails) files using Visual Basic/MS Access
Anyone know how to do this? I've created a CreateObject("NovellGroupWiseSession") object but don't know what to do after that. I'd like to have it open in just one screen so the user can see the email. I've read about a qvp32.exe to 'quick view' the email but I cannot find that executable on my machine. So, another question, if I had qvp32.exe, how can I create an object to do a dos command sort of opening, using something like 'qvp32.exe xxxx.mlm' ? thanks for any help, craig. ...

Copying MS Access object (Forms etc) using VB from 1 db to another
Hello, I have a VB .net program that I would like to be able to to do the following: Open a source Access database Get all the forms, reports, tables etc Copy them all to a second target MS Access database I tried using (in vb .net): TargetDB.DoCmd.CopyObject but I cant seem to get all the parameters right. Can anyone help? Cheers Paul Paul, Do you have this link, it describes a lot of things with Access. Access Automate http://support.microsoft.com/default.aspx?scid=kb;EN-US;317113 Cor ...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290857603)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290857603) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45289360972)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45289360972) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45289459634)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45289459634) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290557627)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290557627) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL DBA efforts. Production experiences as lead Oracle DBA Cluster experience using MS SQL Data modeling using MS Platform ASP .NET VB 6.0 MS SQL Server 2000 Java scripting Experience on large, mission critical client server applications Please send your current resume in confidence to <staffing@eurosoft-inc.com> ..45290557627. ...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290539414)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290539414) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290557627)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290557627) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290832412)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290832412) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290832412)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290832412) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290857603)
US-TX-Austin: Programmer/Analyst, MS SQL, MS Platform, ASP, .NET, VB 6.0, Java s (45290857603) ============================================================================================== Position: Programmer/Analyst Reference: SMC01233 Location: Austin TX Duration: Skills: Production experience as lead MS SQL DBA to include installation, validation, optimization, performance tuning, data security, backup, disaster recovery, redundancy, upgrades and development/documentation of best practices for all SQL D...

Accessing MS ACCESS database using Data Environment
HI, I have developped a program in Visual Basic 6.0 using MS Access database. I have created all my reports through Data Environment where I have entered the location of my database file (for e.g c:\project\app\test.mdb) in the Connection properties. I have installed my application on another PC using Package & Deployment wizard. When viewing the reports on the other PC, I have to enter everytime the new location of the database. How can I solve this problem, please? nadia wrote: > HI, > I have developped a program in Visual Basic 6.0 using MS Access database. >...

Use ASP to read MS Access query that calls MS Access function
Hi: I have an MS Access query for a table called MyTable. One of my fields in the query is a custom field that looks like "MyField: = MyFunction(ID)" where ID is a field in MyTable. I have ASP code and I can use it to read the query OK when "MyField" is not part of the query. If I add in MyField and then run my ASP code to try to read all the query fields, I get an error message on my web browser which says something to the effect of "cannot find MyFunction." On the Access side, I have MyFunction set up in a module and use the keyword "Public" in front of the function name. What else do I need to do to get my ASP code to run this query. I'm trying to avoid reproducing MyFunction within the ASP code if possible. Thanks. Kevin On Thu, 14 Jun 2007 21:38:19 -0000, k-man <kmccrack@gmail.com> wrote: Sorry, no can do. From ASP you don't have access to the Access VBA interpreter. -Tom. >Hi: > >I have an MS Access query for a table called MyTable. One of my >fields in the query is a custom field that looks like "MyField: = >MyFunction(ID)" where ID is a field in MyTable. > >I have ASP code and I can use it to read the query OK when "MyField" >is not part of the query. If I add in MyField and then run my ASP >code to try to read all the query fields, I get an error message on my >web browser which says something to the effect of "cannot find >MyFunction."...

MS Access to vb.net/ASp.net migration
Howdy all. Just wondering if there is a drag&Drop solution for migrating MS Access forms to VB.NET? Anyone had any experiences? On Wed, 14 Jul 2004 16:26:26 GMT, "NavEEd" <naveed010@hotmail.com> wrote: > Howdy all. > > Just wondering if there is a drag&Drop solution for migrating MS Access > forms to VB.NET? > > Anyone had any experiences? I have yet to see any implementation of continuous forms in .NET, so I don't think it's possible to convert those. I also am not aware of a product to convert non-continuous forms, but it would not be too difficult to convert one that had no code attached to events (buttons, before update, etc.). I'd be kind of surpised if there isn't a tool that can do that. I was actually planning to try to write one myself one of these days. I think the hardest part about that would be that the combo box control on .NET is missing some of the features the Access one has, so a custom control would have to be written. "NavEEd" <naveed010@hotmail.com> wrote: > Just wondering if there is a drag&Drop solution for migrating MS Access > forms to VB.NET? Experience no. Links yes. Convert Access to Visual Basic, Delphi, Java, ASP or ASP.NET http://www.granite.ab.ca/access/accesstovb.htm Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tip...

US-TX-Austin: VB .Net programming, T-SQL , MS Access; 6M (45360645366)
US-TX-Austin: VB .Net programming, T-SQL , MS Access; 6M (45360645366) ====================================================================== Position: Programmer II/III Reference: ZYD00208 Location: Austin TX Duration: 6M Skills: Read and write English, read, understand and implement technical instructions or documentation. Intermediate VB .Net programming skills. Intermediate T-SQL programming skills or basic T-SQL with interest in improving skills. Aptitude, and interest in, writing tec...