0

Please tell me there is a better solution to querying a remote Access database!

We are currently using this because thus far we have failed at hooking up a sp_linkedserver. I feel like it's a terrible hack and want to be done with this whole network mapping business once and for all!

    'This is for access 2007
    'DataBase on Local Machine
    '*************************************************************************************************
    'Before a conneciton is made this code sends the credentials to the remote path.
    Dim objNetwork As Object
    objNetwork = Microsoft.VisualBasic.CreateObject("WScript.Network")
    'this line below deletes the drive if there is one so that a correct mapped drive can be created
    'objNetwork.RemoveNetworkDrive("W:")
    Dim DriveLetter = "W:" ' *** This drive needs to be deleted after it is created see the end of this sub for deletion
    Dim RemotePath = "\\volume10\pickles\toads\rocks\sheepy\almostTodb\behindTheScenes"
    Dim UserID = "sysama01\starUser"
    Dim UserPWD = "secretPass"
    objNetwork.MapNetworkDrive(DriveLetter, RemotePath, False, UserID, UserPWD)
    'Syntax()
    '****.MapNetworkDrive(strLocalDrive, strRemoteShare, [persistent], [strUser], [strPassword])
    'Options:
    'strLocalDrive  : The drive letter (e.g. L:)
    'strRemoteShare : The UNC path to the remote drive \\MyServer\MyPrinter
    '(String value)
    'persistent     : True/False - store the mapping persistently in the users profile
    'default = false
    'strUser        :  The user name. (Optional)
    'strPassword    :  The password. (Optional)
    ' Location for code http://ss64.com/vb/drivemap.html
    '*************************************************************************************************
    'DataBase on Network Drive
    objConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=W:\SuperDeliciousFoodDB.accdb;Jet OLEDB:Database Password=databasePasswordHere")
    'DataBase on Network Drive
    objConn.Open()
    objCmd = New OleDbCommand("SELECT * from pancakeTable, objConn)

    objReader = objCmd.ExecuteReader
...
'Do more codestuffs here
objNetwork.RemoveNetworkDrive("W:")
AstroCB
  • 12,337
  • 20
  • 57
  • 73
  • Could you use a web service hosted on the same server as the access database? – John Koerner Jun 13 '12 at 20:35
  • I... don't think so? I'm going to be honest I don't think so because the access database located on the P drive of my computer (and for our entire intranet I believe...). I think the main problem is that there is credentials to get into this particular directory, which I can't seem how to pass along with the OleDbCommand or anything. It's the database of the guy just down the hall... very frustrating to have so much difficulty connecting to it –  Jun 13 '12 at 20:39
  • I don't suppose you can switch to SQL Server express? If the access db still needs to be there for legacy stuff, you could add the SQL server tables as linked tables. – Steven Doggart Jun 13 '12 at 20:43
  • There is this interesting article here. [Connect to a UNC Path with Credentials](http://www.codeproject.com/Articles/43091/Connect-to-a-UNC-Path-with-Credentials). It use the Windows NET API encapsulated into a small class. Hope it helps. – Steve Jun 13 '12 at 20:43
  • @SteveDog: I don't know if I understand the "linked tables" concept. Basically a linkedserver but on a smaller scale? Also, the access db isn't just for legacy stuff (I wish it was). The guy down the hall uses it to keep track of some useful information (that I wish he would just put in our database!) ---Steve: I... am too much of a noob to grasp that stuff I think, I apologize! –  Jun 13 '12 at 21:02
  • @Silver how has adding a linked server failed? – swasheck Jun 13 '12 at 21:03
  • @swasheck: If we didn't go into our particular computer and type in our credentials through that directory beforehand, attempting to connect to the linkedserver would give a 7303 error, saying stuff about not having privileges to view or write. The linked server seemed like the perfect situation, minus the part where we didn't know where to put in the credentials to actually get through the directories to where the actual database was located –  Jun 13 '12 at 21:11
  • @Silver is your SQL Server running under a local system account or a network service account? Even better, is it running under a domain service account? – swasheck Jun 13 '12 at 21:12
  • @Silver Additionally, I would say that (perhaps at levels above your pay grade) influence should be exerted to move this "guy down the hall" onto the SQL Server. Perhaps a web-based UI or even Reporting Services (if he's read-only). Or just take the data you need, roll it into SQL Server and then leave "the guy down the hall" with the (unsupported) leftovers. Shadow dbs are a management nightmare. – swasheck Jun 13 '12 at 21:20
  • @swasheck: So, I'm not entirely sure. It appears we have some sort of development server that runs on 11.12.13.14. We also have a production server that runs on pancakes.waffles.com. We have individual accounts for logging into the remote desktop for 11.12.13.14. The pancakes.waffles.com gets a different error (7399), which we are trying to walk one of the IT guys through (who is 1000 miles away). Ah, if this entire company decided to stick with SQL Server I would be in heaven! –  Jun 13 '12 at 21:45
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12514/discussion-between-swasheck-and-silver) – swasheck Jun 13 '12 at 21:45
  • I can give you the code we used to generate the linked servers, let me find it... –  Jun 13 '12 at 21:46
  • If you moved all the table that are currently in the access db into a SQL Server DB, you could remove those tables from access and replace them with linked tables by the same names. Linked tables appear as if they are the same as local tables, but in reality, the data for the tables are actually in the SQL database. As long as the linked tables still have the same names as the old local tables, I wouldn't think you'd have to fix anything else in the database (forms, reports, queries). All those things would just use the linked tables from SQL and not even know the difference. – Steven Doggart Jun 13 '12 at 23:09

1 Answers1

0

I would suggest migrating data onto SQL Server. If you use the linked tables option, you can continue to use the forms, reports and queries in your existing Access db, while still being able to query the data remotely, from as many connections as you can.

jmoreno
  • 12,752
  • 4
  • 60
  • 91