0

I have been given a task, which is the eventual re-write, but in the meantime, I need to document all that is going on.

We have an Access database that doesn't actually store any data. The Access database is simply the UI (MS Access Forms) that a user uses and the data is actually maintained in a SQL Server database. One thing I cannot seem to find is: the connection string used for MS Access to connect to the SQL Server. I need to find what database / server is used to store the information, but cannot seem to figure this out, nor has Google been able to give me the answers. Would anyone be able to help?

  • http://stackoverflow.com/questions/3581049/configure-adp-application-to-use-a-different-database Found this answer. –  Jul 25 '16 at 17:08
  • And... did it help? For next time, please specify that you have an ADP project - this technique is outdated and hardly used anymore. – Andre Jul 25 '16 at 18:06

2 Answers2

0

Open the Immediate Window (Ctrl+G)

? CurrentDb.TableDefs("a_linked_table").Connect

will give the connect string.

Or open a table in design view and open the properties.

Andre
  • 26,751
  • 7
  • 36
  • 80
  • There are no tables in this Access database. Just forms and some modules, so I cannot do that. –  Jul 25 '16 at 15:08
  • When I try the above in my Immediate Window, I get an error stating "Run-time error 91: Object variable or with block variable not set". I also cannot do CurrentProject.Connection.ConnectionString –  Jul 25 '16 at 15:09
  • Huh. No linked tables? Is this an ADP project? Maybe you have to delve into the code behind the forms to find how the data is loaded. @AMC – Andre Jul 25 '16 at 15:20
  • Yes, this is an ADP project. I tried searching the forms to find the information, but can't seem to find the information. The code does this: Set db = CurrentProject.Connection, but I can't seem to find where it sets the connection. Right mouse click and "definition" doesn't bring it up –  Jul 25 '16 at 16:21
0

you can run this query:

SELECT * FROM msysobjects WHERE connect <> '';

the result is the list of the objects with a connection string that is not empty.

Paolo
  • 2,224
  • 1
  • 15
  • 19
  • Are you saying inside SSMS or Access? –  Jul 25 '16 at 15:07
  • I know for sure that it works when run by Access itself; can't test it from SSMS though – Paolo Jul 25 '16 at 15:12
  • `msysobjects` is an Access system table, so it will only work in Access. But without linked tables, I don't know if it will help. Perhaps there are Pass-Through queries. – Andre Jul 25 '16 at 15:22
  • @Andre I missed the latest comments to your answer: no linked tables = no output. – Paolo Jul 25 '16 at 15:25