0

I'm developing an application in asp.Net using VB and an Access database. My client has specified these, and I can't use more robust tools.

My application has to perform a sizable number of logical operations, and SQL is perfect for this. However, because of some of the limitations of Access SQL, I can't really write large SQL statements that do the whole job. Lacking logic testing like IF-ELSE, I'm stuck writing literally dozens of SQL statements. That would be OK, but I'm leery of all that activity against an Access database. Access isn't very stable when you work it that hard.

I've fooled around with funky solutions using things like the SWITCH function, but they look more like spaghetti than actual code. Wouldn't be maintainable at all.

I can upload all of the data into objects in memory and loop back and forth through them using VB logic, but SQL would sure be more efficient.

My question is: is there some object I can create in memory that I can run SQL against? Some recordset-kind of thing? Came up snake-eyes when I searched for this, but I thought I'd ask.

Thanks for any suggestions.

Stanton
  • 1,334
  • 4
  • 18
  • 32
  • Someone else might have a better answer, but ultimately "is there some object I can create in memory that I can run SQL against?" conflicts badly with "My client has specified these, and I can't use more robust tools.". Seems your client has given you their foot and provided a gun with the instructions of start shooting. There are good free databases out there...sorry if it's not the best help, but maybe your client would be more open to an Access alternate? – Twelfth Jan 22 '14 at 17:17

1 Answers1

1

So if I understand your question correctly you currently have to use an Access database as the backend storage but you do not like doing this and would rather pull all data into the application (ASP.Net) and perform your queries against this as if the database was an SQL database in the application. I expect then that you would push the data back.

AFAIK no this cannot be done. While you could put most of the data into objects and do the manipulation there you will not have the relationships etc. but you could try using LINQ or entity framework.

This link below explains that you can do LINQ with MSAccess and that may give you the query power you want.

Query Microsoft Access MDB Database using LINQ and C#

Community
  • 1
  • 1
Craig Moore
  • 1,093
  • 1
  • 6
  • 15
  • Yeah, probably best just to bite the bullet and do the whole thing in VB. Thanks, though. – Stanton Jan 22 '14 at 17:34
  • Well VB is fine but I pitty you having to stick with Access. SQL Server is just so much better, but I am sure you know that already :) – Craig Moore Jan 22 '14 at 17:37