ms access database table link problem 3

[bslintx]

hello all,

finally, i get to submit a problem..lol anyways i have a personnel table listing all personnel in an organization. i test on a local laptop setup w/iis win xp pro. i "link" the table from another database application so i don't have to create the same database table over again. i'm trying to "centralize" and take advantage of access's linking tables. the problem i have is when i try to do this on my work's intranet. it works great on a local machine (my laptop); however, on the intranet like the laptop let's say i have a personnel roster list within a "roster" folder(which contains the tblPersonnel) and i have another application in another folder that needs to use the tblPersonnel so i try to link to the one in folder roster; however, i get an error saying the path is wrong to get to the linked tblPersonnel. Why can i do this on a developmental laptop server but not on an exact same setup on a lan environment? Logically it's setup identically and is on the same server like the laptop. is there a setting i am missing for linked tables and if so why would there be a difference if 1. my laptop emmulates or actually is a web server using iis vs the same thing on a lan web server. i am accessing the same files. i am not tring to link across the lan...i am physically on the server as i would be on my laptop. I hope this makes since and if anyone has an anser would be greatly appreciated because this will open up a lot of possibilities (until AD) is cleaned up at work. Thanks for the help!!!

BSL

 

[bslintx]

btw...i posted in asp forum because it may be in my asp code...connecting to the ms access database and i am using asp to make this database web based; otherwise i would have posted in access database. thanks

BSL

 

[steven290]

can you post yoour code, and also whats the exact error message you get

 

[PaulBricker]

It should work. I've got three linked tables in my database that I pull info from without any problems (but both db's reside in the same folder on the server also).


Paul

 

[bslintx]

Paul...is yours on an intranet? steven...i don't have the code, but it's a typical connection to the application

ie:

<%
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
FilePath = Server.MapPath("db/users.mdb")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";"

' form or query...whatever
%>

the problem is in the ms access databse itself...when you link to it you get the link icon. works fantastic on development but not on an intranet...<scratching head> i'll have to sit on this until tomorrow steven to get you the error that comes up....i'm not at work. appreciate the quick response...and i'll keep you 2 posted. Thanks a lot!

Brian

 

[bslintx]

heck...i may vpn later to get that error steven...but if not tomorrow

Brian

 

[PaulBricker]

Brian, it is on an Intranet. Here is my connect code

<%
Dim myInput
myInput = request.form("myID")

strQuery ="Select * FROM tblPin Where tblPin.[PIN] = " & myInput
Dim objConn, dbPath
  dbPath = "C:\WEBSITES\physplantdb\PysPlant2002.mdb"  'this is the database with linked tables
  Set objConn = Server.CreateObject("ADODB.Connection")
  strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    objConn.Open strProvider
Set rst = Server.CreateObject("ADODB.recordset")
    rst.Open strQuery, strProvider
%>

Paul

 

[bslintx]

Paul,

That rings a bell on the error code...it's looking for the C: drive....all other applications I have running does not require this. You think this is the problem and if so why?


The following is the way i would have set it up...since it's in the same directory didn't think i needed the c:...hmmm, i'd crap if that's the solution...lol
<%
Dim myInput
myInput = request.form("myID")

strQuery ="Select * FROM tblPin Where tblPin.[PIN] = " & myInput
Dim objConn, dbPath
dbPath = "db\users.mdb" 'this is the database with linked tables
Set objConn = Server.CreateObject("ADODB.Connection")
strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
objConn.Open strProvider
Set rst = Server.CreateObject("ADODB.recordset")
rst.Open strQuery, strProvider
%>

Thanks Paul,

I'll let you know!

Brian

 

[PaulBricker]

I can't tell you about the path other than that's what I currently have as my path. We've been thru a lot of changes here and I'm not the server guy. That path was an old path from early on and I know that there is another path that does work as well.

\\bsample\physplantdb\PhysPlant2002.mdb

is the virtual path (?). As I say, I'm not the server guy. I do the webpages, he puts them where they belong. I just know that both paths work and I've never asked why. Maybe I will tomorrow, but usually I'm fairly confused by any answers I get from them.

Paul

 

[steven290]

bslintx,
our method is missing the server.mappath, and you are using backslashes instead of forward slashes, also when you are trying to open the recordset you are mixing up the connection string with the objConn object (should be rst.Open strQuery, objConn), but i will ust the execute method so it doesn't matter

<%
Dim myInput
myInput = request.form("myID")

strQuery ="Select * FROM tblPin Where [PIN] = " & myInput
Dim objConn, dbPath
  dbPath = server.mappath("db/users.mdb")  
  Set objConn = Server.CreateObject("ADODB.Connection")
  strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    objConn.Open strProvider
Set rst = objConn.execute(strQuery)
%>

 

[bslintx]

ok here's the deal:

1.) I AM linking a table successfully from a database on
server1 to a database on server2 across the network
2.) I know this by seing the link on the table and I can
open and view the data with no problems
3.) I use the follwing asp connection code to connect to
the database on server 2 (one w/ linked tbl):

<%
strQuery ="Select LastName FROM tblRoster1;"'linked table
Dim objConn, dbPath
  dbPath = server.mappath("db/test.mdb")'db w/linked tbl
  Set objConn = Server.CreateObject("ADODB.Connection")
  strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    objConn.Open strProvider
Set objRS = objConn.execute(strQuery)

Do While Not objRS.EOF

Response.Write objRS("LastName") & "<br>"' field in linked tbl

objRS.MoveNext
Loop

objRS.Close
%>

result:
Error Type:
Microsoft JET Database Engine (0x80004005)
The Microsoft Jet database engine cannot open the file '\\server1\roster\db\roster1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

NOTES: checked w/ web...opened to all(test) no restrictions and still same problem

No one knows about this test db; therefore, it is not opened by a user

Steven - Pauls mthod is missing server.mappath not you and
the /\ will work either way just to let you know...
anyways:

Tried w/o server.mappath and got the following:


Error Type:
Microsoft JET Database Engine (0x80004005)
'C:\WINNT\system32\db\test.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
/test/connect_db.asp, line 10

Paul...I'm guessing all your databases are on a server and you're linking on the same server...if not and set up like me..how are the permissions set up....i don't understand why it woon't work...unless the asp connection is lacking something...like I said..it is linked and i can open up in access itself...but not in asp...Thanks!

PLEASE HELP......weird man...weird

 

[PaulBricker]

Have you tried this

strQuery ="Select LastName FROM tblRoster1;"'linked table
Dim objConn, dbPath
dbPath = "\\server1\roster\db\roster1.mdb"
 Set objConn = Server.CreateObject("ADODB.Connection")
  strProvider = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & dbPath
    objConn.Open strProvider
objRS.Open = strQuery, strProvider

Both my databases are in the same folder, on the same server. I couldn't tell you anything about the permissions because I don't get involved with that.
Sorry I can't think of anything else.


Paul

 

[steven290]

now whats the reason you are doing, why not just use server1 as the host machine and just call the page from server2's browser via ip address(ex.

http://ipofserver1here/webapp/something.asp)

 

[bslintx]

steven,

i am trying to use server2 as the host machine machine; hence, why i linked to a tbl within it with server1. Let's see if i can elaborate it a bit further....sounds like you or Paul are lightyears over me in experience so perhaps you two can come up w/ a better plan on what i am trying to do here...

ok,

server2 contains a tblRoster that lists all personnel within 1 organization and their phone/dept/rm/building etc information(450). and will contain many more personnel and orgs in the future; potentionally getting to 20,000 personnel (yes, i will transfer to ms sql server after development is completed) we have ms outlook and of course you can use the global to get the phone # dept etc but the problem we're having is that the phone number is usually generic...like a help desk # for example and not the actual number for that personnel....so i came up with an idea that would allow (upon permissions) for any unit to look at its personnel's true phone number and alleviating trying to track down personnel. we have AD; however, it's tracking over 25,000 users and is filled with erroneous information and also we are limited to WHAT info we can get out of it. so, in theory/planning have a host server containing all personnel on centalized location and can have anyone that has an application (database) to be able to use its info to populate personnel information instead of having to create a personnel roster over again. this is why i thought i could take advantage of linking to its table. the table is quite large and can be shared out to smaller apps needing it's info from any location as long as it has access (authenticated) on the intranet. as i see what they are doing now is copying the "structure" of the database and putting it on their servers and having the personnel populate it over and over again. needless-tosay i thought this was ridiculous. i read up on access and it CAN be linked across a network and it certainly does because sure wnough on server 2 i have the linked tblRoster from server1 and can view it's information. no deleting capabilities but that's no problem, it should be read/only anyways. it has a link manager and the link is EXACTLY where the original table resides however when trying to connect to the server w/ the linked table and pull the records for tblRoster you see the generated error above. So, either have a solution for this or i need to start thinking outside of the box like you guys do. unfortuantely i am limited to asp skilss even though i have all these great ideas.

in a nutshell,

what is suggested in this situation? no link or your idea (steven) and to be honest not quite sure how yours works. please excuse my ignorance...again...learning but i learn fast. you and Pail's patience has been a godsend and i truely appreciate the help. i know i wrote a narrative but this way you know EXACTLY what i am trying to do.

Thanks,

Brian

 

[bslintx]

Thanks to Paul and Steven! Actual fix action was from steven using network connection links to vd on iis server. No need to link an access table in this situation ...will degrade performance in the long run since access performs better within its own app not over a network. Talented guys here and truely appreciate the help. Star for both..Paul thanks for the efforts and sticking w/ it and Steven...thanks for the fix action! Kudos.

Brian

 

[PaulBricker]

Thanks for the star Brian but I didn't do much. Once you get into the servers, I'm out of the loop, so to speak. We only have so much time in this life and mine doesn't include servers. So from me, "nice catch Steven". Have a star.

Paul