Home / server / Questions / 176692
Accepted
Eric H
Asked: 2010-09-01 12:57:25 +0800 CST

Identify Access databases linked to a specific SQL server

  • 772

Is there a way to parse .mdb files from the command line (findstr or via a WSH script) to identify database links?

I am attempting to identify all .mdb files within a large file share that link to a specific database server. I've tried manually searching a few .mdb files that link to the database server within a hex editor, but a search on the database server name doesn't return any hits.

The file server is running Windows 2003.

Update:

I found some text in my text editor (TextPad) that looks like embedded ODBC information: ..."S.E.R.V.E.R.=.m.y.s.e.r.v.e.r"... I'm not sure what encoding is being used, findstr is unable to locate this text within the file.

scripting microsoft-access

3 Answers

  1. Best Answer

    FINDSTR doesn't handle Unicode strings. If you can't use other utilities, you can get real crappy output by doing this: type dbname.mdb | findstr "S E R V E R ="

    If you can use another program, download the SysInternals "strings" utility here: http://technet.microsoft.com/en-us/sysinternals/bb897439.aspx

    And the do something like: strings -u dbname.mdb | findstr "SERVER="

    • 1

  2. Using PowerShell you probably can. See Richard Siddaway's Blog postings with the Access tag. I would think that you can read through all the table definitions connection string and figure out the appropriate values. The following is the code I use in VBA.

    For i = 0 To db.TableDefs.Count - 1
    If Len(db.TableDefs(i).Connect) > 0 Then
        TableConnectStrnig = Mid(db.TableDefs(i).Connect, 11)
    End If
Next i
    • 1

  3. As far as I can tell, there is no answer to this question. Access databases (usually .mdb files) are standalone, not connected to a SQL Server (or other) server-based database.
    This is not correct. Connections to other databases can be created inside Access. I read and started typing too quickly, presuming server-driven situation.

    It is possible that data can be extracted or exported from a server-based database and stored as an Access database. It is possible to import an Access database into a server-based database. It is even possible for MS SQL Server to read from and write to an Access database file through Remote Stored Procedures or "Link Tables".

    But there is no required server connection in an Access database, and unless coded specifically, nothing in the Access database to identify a particular server to which it is or has been connected.
    --> This is not completely correct .. an ODBC or other connection object inside the Access database should have information about the other end of the connection. I don't believe that info can be seen other than through the Access environment or a program, however.

    EDIT/UPDATE: The question was specific .. can the .mdb file be parsed with a command-line or text search tool to find database connections. While my answer did not really consider the "front-end" database scenario, I think is still stands .. I don't think you can interrogate the database in the manner described in the question. You can probably write a program to interrogate the Access database and get connection properties. But that wasn't what was asked.

    • 0