Combine Visual Studio with Access

Combine Visual Studio with Access

• ccsfiona

  Adamus,

  The database is already in Sql server 2005. My problem is that when Access is connected to the server the VB app throws me a connection error.

  Ideefiks

  
  

• lonskie1030

  Use the Access2002 upsizing Wizard and put it on the SQL Server.

  Tools >> Database Utilities >> Upsizing Wizard.

  Adamus

  
  
  

• jw700

  Adamus,

  I have 2 completely separate files, one is in Access, the other is a visual basic project (NOT VBA within Access). Both can connect to the server but not at the same time.

  Ideefiks

  
  

• jasalandra

  Wizards and fancy IDE's often can save a lot of work that would otherwise have to be hard coded. This is well known. However, without understanding what the code is doing behind the scenes, it's sometimes impossible to pinpoint the error.

  If you're certain you've asertained the root cause, hard code it and see if the result changes.

  Adamus

  
  
  

• Zappo

  I'm not sorry to say that I haven't. With the various bad reviews that .adp received, I chose not to experiment with its bugs either known or unknown.

  I'm guessing your project is a personal one and not work related simply due to the strictly limited control it allows and the aforementioned.

  If, by some freak chance, you are maintaining or upgrading such a poorly programmed approach, I do not think you'll find much help here. (and I'm hoping this isn't your design)An Access forum such as www.accessmonster.com would be a much better location.

  If you're just "playing around" to find the bugs, you'll find many googled articles on them and will probably encounter a few on your own.

  To further elaborate on your question(s), regardless of the language or technology, of course you can connect to many datasources, but you must keep in mind that the connections will be unque to each one. There is no "one connection fits all."

  Also, and finally, I think you'll find a much higher success rate and higher quality responses to your problems if you post some code along with your situation.

  Adamus

  
  
  

• Adrigo Gallus

  It should allow concurrent connections. Are you accessing the same table If so, make sure your locktypes are allowing dirty reads.

  Use (nolock) on the table

  Select * from Customers (nolock)

  Adamus

  
  
  

• Amit Pandey

  Damn, the solution was painfully easy, you can set the initial catalog property in addition to the attachdbfile property. Visual studio then reattaches to the previously attachment and doesn't mess anything up ...
  
  Thanks,
  Ideefiks
  
  

• Tamer Shaaban

  Adamus,

  That was indeed what was causing the problem, the VB project reattaches the database every time it runs (and autonames it to the full path of the physical file), messing up a previous attachment of the database.

  The modify connection dialog requires a database file name and cannot be set to use the initial catalog property. Should I set the connection through code to avoid this in the future

  Thanks for the effort.

  Ideefiks

  
  

• Stephan Ruhland

  Adamus,

  I was asked if an .adp could be connected to the database so that an Access user could query the database and generate reports.

  Looking at the connectionstring in the Visual Basic project I see that the AttachDbFilename property is set. Could it be that this is the cause of all problems, the VB project reattaching the database every time it runs

  In this particular case no code was used to set the connections.

  Ideefiks

  
  

• J-own

  Adamus,

  The .adp file IS connected to SQL server, I can open it and edit data. My problem is that I have another application, developed using visual basic 2005, that also connects to the server, but cannot do so when the .adp file is open.

  Ideefiks

  
  

• kiranv

  The error is being caused by trying to open a connection that is already open. This will throw an exception. The same would result if you tried to close a connection that is not open.

  Use Try, Catch, Finally to check the connection status.

  Adamus

  
  
  

• kawano1h

  Adamus,

  Have you ever connected an access .adp file to an sql server 2005 express database It seems that there is an incompatibility between the two.

  Do you know if it is possible to connect an visual basic 2005 express project to another datasource then sql server 2005 express

  Ideefiks

  
  

• desijays

  Ideefiks wrote:

  However when i open the .adp file i get a connection error when trying to run the VB app and I need to run both simultaniously.

  Here's the problem...you can't open the .adp file and then try to connect to it with VB. It must be closed.

  Adamus

  
  
  
• Combine Visual Studio with Access