Member Avatar for maurice91

Hi,

I have a Vb.Net solution that contains several forms that read/write to an MS Access database.

My problem:

When I copy my Vb.Net solution and Access database to a new computer, the data source connection string needs to be changed. Currently the data sources wizard sets the connection string.

Is it possible to allow the user specify the connection string of the dataset or is there a different, better solution available?

Thanks for your time.

  • 4 Contributors
  • 6 Replies
  • 218 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Lethugs

Dani AI

Generated

Short answer: yes — make the app ask once at startup, persist the choice, and have every data-access object read the saved value rather than relying on the wizard's hard-coded string.

This builds on (save the user's choice) and (present a dialog). Practical plan and where to put the code: run the prompt/validation in the application startup (MyApplication_Startup or a Sub Main) so no form tries to use the DB before the connection is set. Persist only what you need (usually the DB file path or a user-scoped setting) with My.Settings and call My.Settings.Save().

Use a single place to apply the saved connection to your typed TableAdapters or other data objects before any Fill/Update calls. If the designer-created connection setting is application-scoped it cannot be changed at runtime — either change that setting to User scope or, safer, explicitly assign the Connection.ConnectionString on each TableAdapter at startup.

Example pattern (simplified):

' run at startup: prompt if My.Settings.DbPath empty, save it
' then build a connection string from the saved path and apply it:
Dim connStr = BuildConnectionStringFromPath(My.Settings.DbPath)
Dim ta As New MyDataSetTableAdapters.CustomersTableAdapter()
ta.Connection.ConnectionString = connStr
ta.Fill(...)

Quick checklist / cautions:

  • Validate the selected file immediately (open a test connection or check for expected tables) before saving.
  • Access drivers are bitness-sensitive; either ship/install the matching Access Database Engine or target x86 if you rely on the ACE/Jet provider.
  • For multiple users or heavy concurrency, consider moving to a server DB (SQL Server) rather than sharing an Access file.
  • Provide a small Settings dialog so the user can re-select the DB later; keep the connection code centralized so every form automatically picks up changes.

Recommended Answers

All 6 Replies

Member Avatar for Reverend Jim

The connection string will have parameters like

dbq (the file name)
uid (user ID)
pwd (password)

You can prompt the user for them the first time then save the resulting connection string (or the individual parameters) in Settings variables to preseve them between sessions. The actual connections string will also depend on whether you are using OLEDB, SQL, ADO, etc for access. For example, ADO might be

Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\temp\my.db;Uid=John;Pwd=Joshua;
Member Avatar for tinstaafl

Probably a form, maybe a dialog form, with several textboxes lablelled with the various pieces of information you'll need to make the connection string(i.e. server, login, name of the database, etc.). Of course you'll have to validate each piece of info to make sure it's in the right format.

Member Avatar for maurice91

Thanks for the responses.

This is my connection string:

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source="C:\Users\Maurice\Documents\Visual Studio 2010\Projects\vbass3\vbass3.accdb"

I don't need a username/password for my database.

Where would I write the code that prompts the user to browse to the Data Source?

Member Avatar for Reverend Jim

You could just use a file browser control on form load and ask the user to select the appropriate *.accdb file. Of course, you'd have to ensure that the selected file is one that actually contains the correct tables. Remember to set the file mask to restrict the selection to accdb files.

Edited by Reverend Jim
Member Avatar for tinstaafl

oops didn't realize it was solved lol

Edited by tinstaafl
Member Avatar for Lethugs

how about my case related to this?

Click Here

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.