If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
need access to read to SQL db and Access frontend
We have a regional dbA. I was hired to create a new dbB for our location
because my supervisor doesn’t like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn’t need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to print the queries I get the message doc_tblObjects already exists? I could print one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don’t know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table …? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, |
#2
|
|||
|
|||
need access to read to SQL db and Access frontend
Starting from the back - dbo_ is the usual prefix for SQL server tables. It
is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, |
#4
|
|||
|
|||
need access to read to SQL db and Access frontend
Why is someone a pro if they don't use macros? Is that because they should
be using VBA or because if they have a good design they won't need macros? Can you do this domain name thing with a mde file on terminal services? I can look and see if I can find the domain name. I suppose I must have it if I can log on to it. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, Why |
#5
|
|||
|
|||
need access to read to SQL db and Access frontend
I cannot get the documentor to print. Either because it is terminal services
or because it is web based or because it is an mde. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, |
#6
|
|||
|
|||
need access to read to SQL db and Access frontend
The weird thing is I looked at it about a month ago and I could print the
field definitions. Now I cannot print anything in the documentor. I don't know if it got changed but I don't know why someone would do that. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, |
#7
|
|||
|
|||
need access to read to SQL db and Access frontend
The fact that there are no macros doesn't mean that the database was
developed by a pro but the fact that there are numerous macros indicates that the designer was NOT a pro. Professionals do not use macros for two reasons, 1. Macros do not support error handling. 2. They know how to code and so can do things much more efficiently and with a lot less trouble by using VBA. Yes, as long as you can get to the database container, you can see the name of the DSN that was used to link the tables. If you have the option to show system tables (Tools/options/ check the show system tables box), you can open the MSysObjects table and see the entire connection string much more clearly than you can with the mouseover. "Janis" wrote in message ... Why is someone a pro if they don't use macros? Is that because they should be using VBA or because if they have a good design they won't need macros? Can you do this domain name thing with a mde file on terminal services? I can look and see if I can find the domain name. I suppose I must have it if I can log on to it. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, Why |
#8
|
|||
|
|||
need access to read to SQL db and Access frontend
Does the documenter product a report and you just can't print it or is the
documenter just not producing a report? I can't help you with printer issues with Terminal Services, you need to talk to your tech people about fixing that. "Janis" wrote in message ... The weird thing is I looked at it about a month ago and I could print the field definitions. Now I cannot print anything in the documentor. I don't know if it got changed but I don't know why someone would do that. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, |
#9
|
|||
|
|||
need access to read to SQL db and Access frontend
I just tried the documentor and it seemed like it was trying to format the
pages. Perhaps I did it wrong the second time. I asked for the relationships and tables so it was taking some time. I will try it later but I'm trying to figure out how to see the linked tables if that is possible. thanks, "Pat Hartman (MVP)" wrote: Does the documenter product a report and you just can't print it or is the documenter just not producing a report? I can't help you with printer issues with Terminal Services, you need to talk to your tech people about fixing that. "Janis" wrote in message ... The weird thing is I looked at it about a month ago and I could print the field definitions. Now I cannot print anything in the documentor. I don't know if it got changed but I don't know why someone would do that. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, |
#10
|
|||
|
|||
need access to read to SQL db and Access frontend
Pat:
I don't know what you mean about getting to the database container. We are in a different building presumably on a different network. However we are linked because of the terminal services. That is all I can see of this database and the security is tight. However, we have a network technician who works in this building and I am sure he has the Domain Name SErver. I was wondering if I ask him for that if I can put that name into the dialog box. In other words, I go to external databases and in the box should I put in the domain name server, IP address or do I need the network address? I can definitely try it to see if it works. At this point however I cannot get to that container by "my network places" those appear to be blocked off. Thanks for any help. "Pat Hartman (MVP)" wrote: The fact that there are no macros doesn't mean that the database was developed by a pro but the fact that there are numerous macros indicates that the designer was NOT a pro. Professionals do not use macros for two reasons, 1. Macros do not support error handling. 2. They know how to code and so can do things much more efficiently and with a lot less trouble by using VBA. Yes, as long as you can get to the database container, you can see the name of the DSN that was used to link the tables. If you have the option to show system tables (Tools/options/ check the show system tables box), you can open the MSysObjects table and see the entire connection string much more clearly than you can with the mouseover. "Janis" wrote in message ... Why is someone a pro if they don't use macros? Is that because they should be using VBA or because if they have a good design they won't need macros? Can you do this domain name thing with a mde file on terminal services? I can look and see if I can find the domain name. I suppose I must have it if I can log on to it. "Pat Hartman (MVP)" wrote: Starting from the back - dbo_ is the usual prefix for SQL server tables. It is short for database owner. So it is possible for the prefix to be different. Local Access tables are frequently prefixed with tbl. You can tell the source of the table by the icon to its left. Squares that look like datasheets are local tables, datasheet squares with arrows to the left are linked Jet tables. Globes with arrows to the left are linked ODBC tables, excel symbols with arrows to the left are linked excel sheets. So, anything that is linked will have a right facing arrow to the left of the type symbol. If you roll the mouse over a linked table, you should see a tool tip with the path to the source database or DSN for the ODBC database. If the tables are linked to a SQL Server database or even another Access database, you will not normally see the relationships in the relationship window of the FE. You would need to view them in the back end database. Most developers don't bother to define the relationships in the FE since they would be descriptive only. Only the relationships stored in the physical database are used to enforce RI. I feel your pain (and your bosses) but you may not be able to get a usable copy (unsecured .mdb) from the developer. An .mde will not let you view any code or any design for forms and reports. It will let you view queries and tables though and macros (although if your developer is a pro, there won't be more than 2 or 3 of them. If there are lots of macros, your developer is not a pro.) Is the documenter available in the .mde? Have you tried running it? I don't like to encourage this, but you may be able to create a new empty database and link it to the SQL Server back end if you can identify which DSN to use. This will allow you to see the linked tables but I think you'll need Enterprise Manager (for SQL 2000) or SQL Server Management Studio (for SQL 2005) to view the relationships. If you open a linked table in design view, Access will allow you to view the primary key and any other indexes but that's about all you can see. Of course, you can't change the structure from an .mdb. Actually, you might be able to create an .adp which gives you a lot more capability where SQL server is concerned so with that, you will be able to see relationships, etc. "Janis" wrote in message ... We have a regional dbA. I was hired to create a new dbB for our location because my supervisor doesn't like the interface for dbA. I believe dbB is a separate module/entity from dbA and doesn't need to be related but I have access to dbA through terminal services. The structure of dbB should be similar to dbA with some differences. I asked the regional dba to give me his structure so I could benefit from the knowledge. All I have is the web interface to an mde file. It took 2 years for the dba to build our department dbA. I noticed that I can't print the fields and tables in the relationships window. I could do a print screen of the field and table names. I don't see any relationships in this window only tables and fields. When I try to the queries I get the message doc_tblObjects already exists? I could one query, presumably because that is the query for the report they give us? I would love to see the reports, the macros, the queries so as to build on what they have already learned. Are there no relationships in the Access dbA because it is only a frontend to a SQL db or because it is an mde file? I think it is stored in SQL because there is a large amount of data or because there are other databases stored and it is easier for the dba. All we need is our tables for our weekly Excel report. Do I need to ask for the SQL database structure as well? Would it help to see the Access relationships? Shouldn't it be acceptable for us to ask him for a copy of the access db itself? We aren't allowed to query this SQL database or read from it. I don't know what his tables and fields mean 1. dbo_XXXX means database open? 2. tbl_XXX means table .? 3. _XXX would this mean a related table or library file? There seems to be a communication problem but maybe I am lucky I get the fields and table names? Tia, Why |
Thread Tools | |
Display Modes | |
|
|