I wouldn't mess with ADOX (tho I imagine you could use com interop if you really wanted to). I'd create it manually in msaccess (empty) as a template & then do a file copy to create the new one. When connected, you have one set of dataadapters that draw data from the db into your dset, and another set that move data from your dset to msaccess. (That's just for your synch process of course--you've got another set of dataadapters to pull data from the .mdb for your UI.)
If this is a LAN app, you can keep the template .mdb on a centrally accessible file share & thereby simplify any updates you may need to make to it. HTH, -Roy -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Jon Rothlander Sent: Wednesday, July 06, 2005 11:17 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net I think we are on the same page now. Your first paragraph explains exactly what I am wanting to do. I figure that I should just rebuild the MDB file each time a sync event occurs between the client and server. Doing so should keep any issues with the MDB file from being a problem. The only MDB issues I have seen are for really large MDB files. I've seen these get corrupted very easily. This shouldn't be a problem for this app since the MDB file is very small and rebuilt often. Of course, if it did get corrupted the next sync event would rebuilt it anyway. I think this is a pretty good approach for what I am doing. The 1% stuff is really simple and can be easily handled with XML. In fact it makes a lot of sense to leave it in XML based on what we have going on with the app. If I can just figure out how to create the MDB programmatically, I think that I will be good to go. Everything on MSDN talks about using ADOX, which I do not seem to have listed in my available references. I have Access 2003 installed on this machine. Should I have the reference to ADOX? -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Pardee, Roy Sent: Wednesday, July 06, 2005 1:05 PM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net Hmmmm... Well, maybe the thing to do is to code up a routine that will simply slurp the authoritative data out of the db & stash it in a client-side .mdb, and then do all your SQL wrangling against the local copy. When your app detects that it's connected (or on some other regular schedule that makes sense), it ditches and re-creates the local .mdb. I think the big risk factor for mdb corruption is having & editing code-bearing objects in it (e.g., msaccess forms/reports/vba modules). I've personally never seen a corrupted data-only mdb. But in any case, to the extent that this is just an offline, read-only cache of data, you don't need to be too attached to it. If it dies a horrible death, c'est la guerre--just re-create it when you're next connected. Of course that doesn't address your 1% not-read-only stuff... -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Jon Rothlander Sent: Wednesday, July 06, 2005 10:41 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net I think my originally posting may have been a little confusing. I realize that there is not SQL engine in ADO.Net, but there is Dataset.Tables().Select(), which I guess is just another way to filter. I simply wanted to point out what kind of work I need to do on the client side. I need to recreate the SELECT statements aginst the dataset or XML. I need the end result of both the client and server apps to be the same. Your idea of stashing the data in local .mdb files may work for me as 99% of what I am doing is read-only. Are there any issues with Access MDB files in .Net? Access is pretty well known for get corrupted. The small amout of updating that would have to be stored locally can be handle via XML. >>And since your disconnected users will only ever be able to get a >>subset of the >>data they got from their last connected query, do you really want to >>encourage the impression that they can run queries when offline? That >>sounds like an invitation to confusion to me... The data that they have access to is VERY small. Most of the 12 or so tables will only have a few dozen records. So I am planning that the users will be able to run queries locally while they are disconnectd. The main point of the app is data entry and the queries are only used to populate controls. Some of the queries get a little complex, which is what I'm having a problem with. I have NEVER needed to do anything complex like this with a dataset. I have always been able to handle it in the SELECT statement and just do some simple filtering on the dataset. Now I need to do a little more complex work on the client side, which is why I running into areas that I have little experience with. -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Pardee, Roy Sent: Wednesday, July 06, 2005 11:31 AM To: [email protected] Subject: Re: [ADVANCED-DOTNET] ADO.Net There is no client-side SQL engine in ado.net. I think the thing that comes closest is the jet engine (the thing that gets exercised when you move data in & out of an .mdb file). Jet has a pretty nice implementation of SQL, tho there are some annoying limitations (subqueries come to mind). I'd advise stashing your offline data in an .mdb and just swapping out DataAdapters as users connect & disconnect, except that I think that would mess up ado.net's updating logic--you'll have to manage RowStates your own self when time comes to push offline changes back to the db. I don't know if that's more trouble than its worth. And since your disconnected users will only ever be able to get a subset of the data they got from their last connected query, do you really want to encourage the impression that they can run queries when offline? That sounds like an invitation to confusion to me... HTH, -Roy -----Original Message----- From: Unmoderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Jon Rothlander Sent: Wednesday, July 06, 2005 8:52 AM To: [email protected] Subject: [ADVANCED-DOTNET] ADO.Net I've doing what I would think would be a very common use of ADO.Net, but I cannot find much info on this in any of my book or online. What I'm doing seems pretty simple. I have an app that can run in both a disconnted and connected state. When it is disconnected, it is disconnected from the SQL DB. My client machine does not have access to a local database, so I am using XML on the client. I am loading the XML into datasets to be processed. The problem I'm running into is that I cannot easily perform the same SELECT statements against the datasets that I can perform against SQL Server. For example, lets say that I have the following SQL SELECT statement... SELECT * FROM table1 If I bring a copy of table1 from SQL server to the client machine and save it as XML, I can reload the dataset using the local copy of XML. Reload the XML will recreate the same dataset as the SQL SELECT statement created with I was connected to the Server. However, take the following SQL statement.. SELECT ID, rtrim(ltrim(FirstName + ' ' + LastName, FROM table1 WHERE userid = '1' OR userid = NULL What options do I have in loading table1 into a dataset and them recreating this same SQL statement? What if the SQL statement was even more complex with 3 or 4 tables, a few JOINS, etc? Do I have to build the dataset tables individually, then set up relationships, and them perform simple SQL statements one at a time while creating new datasets with each result? That seems to be the way the books show you. However, that seems to be a major pain. I might as well right XML parsing code to handle it. Maybe creating a new dataset from SQL when the user is last connected, but create one with the JOINS already in place. Then just use multiple table().Select () statements to drill into the records I need. What do you think is the best way to handle this sort of logic? Any ideas would be very much appreicated. I want to make sure I approach this one correctly so I do not need to rewrite it down the road. I also want to understand what options I have to make sure I am not missing something that would make this pretty easy to code. Best regards, Jon =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor. http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor. http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor(r) http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
