Member Avatar for bcheath_1

Hi,

I am working on a project that entails one beast of a database. A friend asked me to write a program into which he could catalog all of his cards by sport and then further by player, team, manufacturer, etc.

The tools I will be using:
1) MS Access - For the database backend
2) VB.NET - For the GUI

I know MS Access will do the job as far as the database goes, but since my friend lives 6 states away, can I package the Access database inside the VB.NET deployment project? So, when he installs the program on his computer, the connection to the database is made automatically.

I have used VB.NET off-and-on for approximately a year, but have never had to build a database for use with the application.

If you have any suggestions, I would be really appreciative.


***If this is not the right forum for the question please let me know so I can
post elsewhere.

  • 4 Contributors
  • 9 Replies
  • 207 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by cpopham

Dani AI

Generated

Short practical guide that builds on the thread (notes for , , and ):

You can ship an Access file with a VB.NET front end and make the connection automatic—but plan the packaging and runtime environment up front. Put the backend file (MDB or ACCDB) in a predictable, writable location (per-user AppData or ProgramData for all users) rather than Program Files, include it as a content file in your installer, and have the app use a relative connection path so the installer “just works.” Be explicit about which Access format you use (MDB vs ACCDB) because that affects which OLE DB provider is required and the app bitness you must target.

A small VB.NET pattern that keeps the database next to the app and uses a relative path (set this early in startup) is useful:

AppDomain.CurrentDomain.SetData("DataDirectory", Application.StartupPath)
' Jet (MDB)
Dim csJet = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\cards.mdb;"
' ACE (ACCDB)
Dim csAce = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\cards.accdb;"

Deployment and robustness tips: compile the app for x86 if you rely on older Jet drivers; if you need ACCDB support on machines without Office, install the matching Access Database Engine redistributable (bitness must match your app). For multiuser LAN scenarios, split front/back end: put the backend on a network share and give each workstation its own frontend copy. Monitor file size (Access files approach practical limits around a couple gigabytes), schedule compact/repair and automated backups, and avoid long transactions to reduce corruption risk.

Design notes for the “monster” schema: normalize entities (Cards, Players, Teams, Manufacturers, Sets), use integer surrogate keys, add linking tables where relationships are many-to-many (player-team-season), and index search columns (player name, team, set). Store images as files with paths in the DB unless you need embedded blobs. If the collection or concurrency needs grow, plan a migration path to SQL Server Express or a hosted database before performance or size become problems.

Recommended Answers

All 9 Replies

Member Avatar for autocrat

why do you need the VB.net front end?
Can't you just build the front end via Access, either as forms or as an DAP?

(Unless your friend lacks a copy of office, which would be unusual! LOL).

Seriously though, is there a set reason for the VB?

Member Avatar for bcheath_1

The only reason I don't use Access as the front end is the fact that I hate the Forms Designer for Access. I have been unable to figure out how the form sizes work. Access works in inches. How that breaks down to pixels I am not sure. Thus, I have not spent a ton of time working with the Access designer, or doing any research on it. I also dislike the rather small selection of controls it has to offer. Some, for me, are awkward to work with.

Using Visual Basic.NET, for me, is a lot easier, quicker, and opens up a lot more doors for a better looking product. I could do the forms in Access, but prefer not to.

I hope that answers your question.

Member Avatar for autocrat

hmmm... in that case, I don't blame you! If it is too restrictive for you, then use an alternative.

Not sure about connecting to an access db though...... should be able too, but not sure..... I think a friend connect standard vb to access, so I don't see why a net version would be unable to!

Member Avatar for Toulinwoek

I have a followup question that's somewhat related to this:

If I develop a front end with some tool, and connect it to an Access back end, could I distribute the result to someone who does NOT have access? Would I need to include some sort of runtime component and would I (or the user) need to pay for said component?

Member Avatar for autocrat

You'd need a DB engine!
Access has it's own engine... yet the saved files are only data, they don't contain anything else about from the basic tables, table records and the details for relationships and scripts/macro's etc. as well as forms/reports etc.
Sou you'd need to supply something that could access that file type (.mdb).
I remember that you could purchase something from MS to ship Access DB's..... you could export the DB as a standalone application... it would basically include an engine for it!

In short, I think you need a component, or something to access MDB files.

If you hold for a bit... I'll see what I can dig up!

Member Avatar for cpopham

No you do not need a component or anything. The .NET includes the JET driver for Access. This can be pretty handy if you want to create a database in Access and then create the frontend using .NET and not worry about your users messing with the database (if they do not have Access). It can also save your users money, you can create a frontend and the Access backend and the users will not have to pay for an Access license.

Chester

Member Avatar for autocrat

ah.... got there before I could!

Just read about .net having the jet engine!
Oh well... atleast it means you can do it!

Member Avatar for cpopham

Yeah, you can even find code snippets that show you how to create an access database, compact and repair an access database and use SQL statements to create your tables and relations all from your code!

Chester

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.