How to use VB to access an ACCESS db over the network 2

[iown714]

Hi,
I'm trying to write just a simple VB application that runs on machine X and accesses an Access table on machine Z. I don't really want to map any drives or such thing. Machine X and Z are in the same network so they can see each other. Does anyone know what the connection string would be for the application to access the table?

Let's say that I want to retrieve all records whose customers whose first name is = "mark". The basic structure to open a db is...

Set dbsCurrent = OpenDatabase("xxxx")

... and then what??? what is the next thing that I have to do to get all the records from machine Z to machine X? What is the exact connection string that I have to use in order to access an Access database over the network? What is the string in the "xxxx" in order to access to the Access database on machine Z?

Thank you in advance.

 

[TLowder]

If you don't want to map a drive you still need to share the folder in which the data is located. I would just map the drive. What is the reason you don't want to map the drive? You can refer to the database through the network name and shared folder name.

Set dbsCurrent = OpenDatabase("\\computerZ\datafolder\database.mdb")

Tom

 

[iown714]

Tom,
Ruffnekk,
thanks for ur earlier response. i just have one last question. i used Access 2000 to creat the table as such it is formatted with Access 2000 "stuff". When I do

Set dbsCurrent = OpenDatabase("C:\data\myDatabase.mdb")

I get an error message that says:

"Run-time error '3343', unrecognized database format C:\data\myDatabase.mdb"

However when i convert the table to an older Access version, like Access 97, the program is able to open the table correctly. have you seen this before? this is very odd, i don't understand why when using Access 2000 to create a table and using the string above i get that error message but when i convert, always using Access 2000, the table to an earlier version of Access then i don't get the error. I have VB 6.0 with Visual Studio 5 SP 6 installed and also Office 2000 SP3 installed. Also in the VB workspace under Project -> References, i already have the Microsoft DAO 3.51 object library checked but i still get this message. do you know if i'm supposed to add some more References in my VB application or if i need to install some other hotfixes? Thanks!!!

 

[TLowder]

Either the database is corrupt which means run compact and repair from access.

Or like you thought its the references. I open my databases differently than you. Here's how i do it -

Dim adoCon As ADODB.Connection

  Set adoCon = New ADODB.Connection
  
  adocon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Database.mdb;Jet Oledb:Database Password=YourPass"  

  adocon.Open
  'record set stuff

  adocon.Close
  Set adoCon = Nothing

Tom

 

[BobRodes]

I think you have to have DAO 3.6 to use Access 2000. Maybe it's time to think about switching to ADO, since DAO probably isn't going to be supported a whole lot longer.

Bob

 

[TLowder]

I would listen to bob. The extent of my knowledge is so minimal compared to bob and strongm and many others. I would like to take this time to thank them for so much help they inadvertly gave me. I've came here and searched for so many things that they answered, without me asking(posting a question) but searching this knowledgebase. Especialy Strongm, its so amazing that the number of posts i've started is so low, but i've logged on so many times and searched for answers i've found to be very helpful. This site rocks but its all thanks to the very helpfull people. Please try to put back what you get out of this site. I wish I had some money because I would definately donate... but I'm just another broke antreprenuer with a dream...

Tom

 

[BobRodes]

Thanks for the kind words, TLowder. For the record, if strongm contradicts something I say, assume I'm wrong.

Bob

 

[iown714]

Thank you Tom and Bob. Bob, i chose as reference Microsoft DAO 3.51 but when i changed it to DAO 3.6 the connection that I used to use... Set dbsCurrent = OpenDatabase("C:\data\myDatabase.mdb") was able to open a table created with Access 2000. I think the DAo 3.6 did it!!! thanks very much Bob. lemme bring out my checkbook. i'm really gonna contribute to this site!!

 

[BobRodes]

Glad we could help.

Bob