Connect to database on one server and reports / forms on another server

My forms and reports are located on one server. Because of an issue I cannot resolve, I need to connect these forms and reports to the MS Access ConnChart2.txtdatabase on another server. My connection string is in an inc file with and include file line included in each report / form:
         <!-- #Include Virtual="xxxxxx/CodeReuse/ConnChart2.inc" -->

My include file code to connect to the database is attached.

How do I modify this code to connect to the database on the other server?
Malloy1446Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you may refer to these resources and see if it can resolve your problem?

Access connection strings
http://www.connectionstrings.com/access/

Microsoft Jet OLE DB 4.0 connection strings
http://www.connectionstrings.com/microsoft-jet-ole-db-4-0/

alternatively, you can create an ODBC connection locally, which connect to that Access database on another server, and then connect your script to that ODBC connection instead, technically it should work.
Vadim RappCommented:
Forms and reports can't be on a server, they are in Access file, which in turn may be connected to the database server. The server only has the data that is shown in forms and reports.
Malloy1446Author Commented:
The attached code is at least locating server B.

<%
' Jet database connection, fastest for Access Database.
Set objConn = Server.CreateObject("ADODB.Connection")

'objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
'Following are 2 lines of code to connect to DB on SERVER A
'Server.MapPath("/xxxxxx/fpdb/data.mdb") & ";"
'objConn.Open


'Following are 2 lines of code to connect to DB on SERVER B
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\xxxxxxx\xxxxxx\fpdb\data.mdb;"
objConn.Open

Open in new window


I am getting the following error:

Microsoft JET Database Engine error '80004005'

The Microsoft Jet database engine cannot open the file '\\vhacowbd2.dva.va.gov\valnet\fpdb\comparison.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

/xxxxxx/CodeReuse/ConnChart2.inc, line 12 (objConn open)

My original code does not use a USERID or PW.

Any ideas what is wrong?

VADIM RAPP: the reports and forms are not part of the ACCESS DB. They are ASP pages using a SQL statement to retrieve the data.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Do you set permission for user IIS_IUSRS to folder \\vhacowbd2.dva.va.gov\valnet\fpdb\?
Malloy1446Author Commented:
yes
Vadim RappCommented:
Look at the management of the machine with Access database, and find out if it's in fact opened.

If it is, then either close the application that opened it, or kill the handle. Then try again.

If it is not, then the issue may be with permissions. The most straightforward way to find out is to run Process Monitor on the target machine and monitor file operations. Another way is using object access audit.

Also, open Access database, go to Options/Advanced, and ensure that Default Open Mode is Shared, rather than Exclusive.

I just tried to open Access 2003 database by two applications at the same time, using your exact code, and they both opened successfully.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Malloy1446Author Commented:
I am having no problem connecting to the database on Server B when I use my asp pages located on Server B.

I am having the problem when I connect from Server A.

 The Default Open Mode is Shared not Exclusive.
Vadim RappCommented:
Can you manually open the mdb on B remotely from A? even in Notepad? can you rename it?
Malloy1446Author Commented:
I've never done that and not sure how.
Vadim RappCommented:
Logon on A, and in Windows Explorer open folder \\vhacowbd2.dva.va.gov\valnet\fpdb . See if you can open it and if you can see the mdb file in it. If you do, try to open mdb file in Access, or even in Notepad (but don't save). Then try to rename it.

If you are not sure how to open remote share in Windows Explorer, ask your network administrator.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
You can try create a DSN connection testing from ODBC Data Source Administrator, and see if it's working to connect to your remote server's Access database.

ODBC 32 bit
%windir%\syswow64\odbcad32.exe

ODBC 64 bit
%windir%\system32\odbcad32.exe

follow the wizard and see if you can connect it properly?
Big MontyWeb Ninja at largeCommented:
jumping in per request of the asker

since you're using a network share to make the connection, you need to make sure you have the correct permissions set. if the asp app is on Server A, then you would be using the account IUSR_ServerA to authenticate everything. Since the DB is on Server B, you probably have the IUSR_ServerB account set, but you need IUSR_ServerA to be able to authenticate.
Malloy1446Author Commented:
Thank you both for your assistance. FINALLY received information from the IIS administrator. They will not allow access to Server B from Server A. So for now nothing works!
Vadim RappCommented:
But you said that you were opening the mdb on remote share, as a file. Then how is IIS in the picture? (if by IIS you mean Internet Information Server).
Malloy1446Author Commented:
I contacted the administrators and asked them to make a connection.


I was wondering if you could set up an ODBC connection that both servers can be accessed by the same user (domain account)
 
OR
 
change the IUSR account in IIS's authentication section to a domain account that both servers have access to. That domain account would need to have access to all of the IUSR accounts that they have locally.


this is the response.

An ODBC connection can only be made to a production database instance. You cannot call on data from an external source that is not production as that is a security violation.
 
As for changing the IUSR account that is also not advisable as well because you could potentially break other websites that are housed on this server.

Unfortunately NOTHING I can do.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.