[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to find SQL 2008 database path and use in code.

Posted on 2010-08-16
15
Medium Priority
?
1,693 Views
Last Modified: 2013-11-28
I have an Access frontend with a SQL Server 2008 backend.  The Server has a LiveSales database as well as TestSales database.
I would like to write code in Access that fills a label box on the main form with "TEST DATABASE" if the current database is anything other then "LiveSales".  I need help with the code/connection string that I need to use.

If Current database is "LiveSales" then
else
   lblTestDb = "TEST DATABASE"
End if
0
Comment
Question by:BobRosas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33450593
You can perhaps dig this out of the Hidden system table: msysObjects

If (DLookup("database", "msysObjects", "Name='SomeLinkedTableName'") Like "*LiveSales*") Then
    lblTestDB.Caption="Live Sales"
Else
    MsgBox "Test Database"
End If

The presumes that "LiveSales" is somewhere in the "database" field for this linked table.

You can run a query like this to see all linked tables in the Msysobjects table.
You can use the info in the query to customize this solution to work in your database:
SELECT MsysObjects.Connect, MsysObjects.Database, MsysObjects.DateCreate, MsysObjects.DateUpdate, MsysObjects.Flags, MsysObjects.ForeignName, MsysObjects.Id, MsysObjects.Name, MsysObjects.Type
FROM MsysObjects
WHERE (((MsysObjects.Type)=6));


JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33450601
Code correction:

If (DLookup("database", "msysObjects", "Name='SomeLinkedTableName'") Like "*LiveSales*") Then
    lblTestDB.Caption="Live Sales"
Else
    lblTestDB.Caption="Test Database"
End If

This code again presumes that lblTestDB is infact a true "label" Control

;-)

JeffCoachman
0
 

Author Comment

by:BobRosas
ID: 33455885
Thank you for your comment.  Do you know if there is a specific Reference (Under Tools, References) that I should have checked or something else I need in order for this code to work?  I tried the code you gave me as well as...
SELECT MSysObjects.Database AS LinkPath
From MSysObjects
WHERE (((MSysObjects.Type) = 6));
and it won't even compile.  I get the error "Expected: Case" on line 1 with MSysObjects highlighted.  .  And I get "Expected: end of statement" on line 3 with the semi colon at the end highlighted.  I have never used this code before so I'm sure I'm missing something I just don't know what.
Thanks again for your help!
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33458436
1. What I posted was not "Code', it was the SQL for a query.
So create a Blank new query, go into "SQL View" and paste the SQL in and then run it. (View-->Datasheet View)

2. What you posted above is not the SQL that I posted here: http:#a33450593
What do you get when you run the SQL as I posted it?

JeffCochman
0
 

Author Comment

by:BobRosas
ID: 33458581
Sorry for the misunderstanding.
When I pasted your code into a blank query it does run but it comes up blank.  There is no data at all.
Any thoughts?
Thanks again.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33460359
Then there are no linked tables in the DB.

My presumption was that if this DB had an SQL back end, the tables would be linked (Via ODBC), and as such, would show up in the MsysObjects table.

How are you connecting to the SQL tables?
0
 

Author Comment

by:BobRosas
ID: 33466002
You are correct.  The access version I'm using is Access 2007.  I am linked to SQL Server 2008.  I'm using an ODBC connection but it's SQL Server Native Client 10.0.  Would that be the problem?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33467529
In Access, can you open the linked table manager and see the SQL tables?
0
 

Author Comment

by:BobRosas
ID: 33467721
Yes I can.  The linked tables have an arrow and the globe and those that are not linked just have a grid icon.
LinkedTalbes.JPG
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33467845
And are these Linked tables visible when you run this:

SELECT *
FROM MsysObjects

If so, then you must check the "Type" field.
For standard Linked tables, it is 6
0
 

Author Comment

by:BobRosas
ID: 33468116
When I run...
SELECT *
FROM MsysObjects
I get 797 records but none of them have a type 6 and the field "Databasae" is blank for all records.
It does give the name of the database in some of the connection strings.  I could try parsing that out but I'm not sure that's the most reliable way to go.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 33468268
Yes, that was my suggestion, If you could get some way of distinguishing beteewn the connections of Test and LiveData, you would be able to determine what you were connected to.

Give it a try and see what happens.

Jeff
0
 

Author Comment

by:BobRosas
ID: 33468337
Thanks!
I wasn't sure it was ok to do it that way but I will give it a shot.
Thank you for all your help.
You hung in there and you were very helpful so I am going to max out your points.
I will post another question if I need more help.
Thanks again!
0
 

Author Closing Comment

by:BobRosas
ID: 33468346
Thanks so much!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33468743
And thank you for an interesting and thought provoking question...

;-)

Jeff
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question