BobRosas
asked on
How to find SQL 2008 database path and use in code.
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
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
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
If (DLookup("database", "msysObjects", "Name='SomeLinkedTableName
lblTestDB.Caption="Live Sales"
Else
lblTestDB.Caption="Test Database"
End If
This code again presumes that lblTestDB is infact a true "label" Control
;-)
JeffCoachman
ASKER
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!
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!
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
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
ASKER
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.
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.
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?
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?
ASKER
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?
In Access, can you open the linked table manager and see the SQL tables?
ASKER
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
LinkedTalbes.JPG
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
SELECT *
FROM MsysObjects
If so, then you must check the "Type" field.
For standard Linked tables, it is 6
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
Thanks so much!
And thank you for an interesting and thought provoking question...
;-)
Jeff
;-)
Jeff
If (DLookup("database", "msysObjects", "Name='SomeLinkedTableName
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