OpenReport from an External Database

[mfaisalwarraich]

Hi Everyone,

I made a database named MainDatabase (with several reports based on several queries) and kept that database on the server. now i made another database named NewDatabase and made a form to insert values into MainDatabase using connection, which is explained below:

Dim ExtDB as Database
Dim strPath as String

strPath = "\\server\MinDatabase.mdb"

Set ExtDB = DBEngine.Workspac(0).OpenDatabase(strPath)

(i appologize if there is any spelling mistake in the code above)

This code is working when i updating values using Recordset. But i failed to understand how i can open the report of MainDatabase in my NewDatabase.Is there any procedure or code by using which i can open/view the report.

Please note that Server is located on local network.An expeditous reply is requested.

Thanking you.


Regards

Mohammad Faisal

[ADezii]

Hi Everyone,

I made a database named MainDatabase (with several reports based on several queries) and kept that database on the server. now i made another database named NewDatabase and made a form to insert values into MainDatabase using connection, which is explained below:

Dim ExtDB as Database
Dim strPath as String

strPath = "\\server\MinDatabase.mdb"

Set ExtDB = DBEngine.Workspac(0).OpenDatabase(strPath)

(i appologize if there is any spelling mistake in the code above)

This code is working when i updating values using Recordset. But i failed to understand how i can open the report of MainDatabase in my NewDatabase.Is there any procedure or code by using which i can open/view the report.

Please note that Server is located on local network.An expeditous reply is requested.

Thanking you.


Regards

Mohammad Faisal

You can use a little Automation.

1. Declare a Module Level Variable
   

   Dim appAccess As Access.Application

2. Execute the following code where appropriate.
   

   Const strDBPath = "\\server\MinDatabase.mdb"
    
   'Create new instance of Microsoft Access.
   Set appAccess = CreateObject("Access.Application")
    
   'Open database in Microsoft Access window.
   appAccess.OpenCurrentDatabase strDBPath
    
   'Open Report.
   appAccess.DoCmd.OpenReport "<Your Report Name>", acViewPreview, , , acWindowNormal

3. Close the Database containing the Report, Quit the Instance of Access, release Resources assigned to it.
   

   appAccess.CloseCurrentDatabase
   appAccess.Quit
   Set appAccess = Nothing

[mfaisalwarraich]

Thank you for your great reply. This example would really help me to know new ways to connect external databases.

The code you gave me is working nicely. But there is one thing which i wana eliminate that is i want to view the report only. This code is enabling the user to open each n every object of the database. I just wana open a particular report.

How i can achieve this?

Thanking you.

regards.

Mohammad Faisal

[ADezii]

Thank you for your great reply. This example would really help me to know new ways to connect external databases.

The code you gave me is working nicely. But there is one thing which i wana eliminate that is i want to view the report only. This code is enabling the user to open each n every object of the database. I just wana open a particular report.

How i can achieve this?

Thanking you.

regards.

Mohammad Faisal

Here you may run into serious problems. Why not simply change 1 Argument
and Print the Report instead of viewing it, as in:

appAccess.DoCmd.OpenReport "<Your Report Name>", acViewNormal, , , acWindowNormal

[mfaisalwarraich]

Thank you again. This will serve my purpose.

I am now trying to populate a combo box with the field of an external database. But I failed achieving this. I shall be grateful to you if u would kindly help me in this matter also.

Thanking you.

Regards

Mohammad Faisal

[ADezii]

Thank you again. This will serve my purpose.

I am now trying to populate a combo box with the field of an external database. But I failed achieving this. I shall be grateful to you if u would kindly help me in this matter also.

Thanking you.

Regards

Mohammad Faisal

1. What is the Name of the External Database?
2. What is the Absolute Path to the External Database?
3. What is the Name of the Table in the External Database which contains the Field data to populate the Combo Box?
4. What is the Field Name and its Data Type?
5. What is the Name of the Combo Box and the Name of the Form on which it resides?
6. Should the values in the Combo Box be Unique?
7. Should these values be Sorted, and if so, Ascending or Descending?
8. What Version of Access are you using? (Critical question).
9. I'll check back tonight and see if you have answered these questions.

[mfaisalwarraich]

1. What is the Name of the External Database?
2. What is the Absolute Path to the External Database?
3. What is the Name of the Table in the External Database which contains the Field data to populate the Combo Box?
4. What is the Field Name and its Data Type?
5. What is the Name of the Combo Box and the Name of the Form on which it resides?
6. Should the values in the Combo Box be Unique?
7. Should these values be Sorted, and if so, Ascending or Descending?
8. What Version of Access are you using? (Critical question).
9. I'll check back tonight and see if you have answered these questions.

I like to reply you as under:

1. Name of the external database is MainDatabase.
2. Absolute Path of External Database is "\\server\MainDatabase.MDB" (local
network path)
3. The Name of the Table in the external database which contains the field data
to populate the Combo Box is "tbl_Unit"
4. Field Name is Units and its Data Type is String (text)
5. The Name of the Combo Box is cboUnit and Form's Name is frmUnitSelect
6. Yes the Values of this combo box will be unique
7. Yes these values will be sorted Ascending.
8. Version of MS Access is 2002.

Thank you for your prompt replies.

Regards

Mohammad Faisal

[ADezii]

I like to reply you as under:

1. Name of the external database is MainDatabase.
2. Absolute Path of External Database is "\\server\MainDatabase.MDB" (local
network path)
3. The Name of the Table in the external database which contains the field data
to populate the Combo Box is "tbl_Unit"
4. Field Name is Units and its Data Type is String (text)
5. The Name of the Combo Box is cboUnit and Form's Name is frmUnitSelect
6. Yes the Values of this combo box will be unique
7. Yes these values will be sorted Ascending.
8. Version of MS Access is 2002.

Thank you for your prompt replies.

Regards

Mohammad Faisal

Okay Mohammad, here we go. Any questions please feel free to ask. The code has been thoroughly tested and is fully functional. Good luck, and let me know how you make out.

Const conPathToExternalDB As String = "\\server\MainDatabase.mdb"
Dim wrkJet As Workspace, strSQL As String
Dim dbsMain As DAO.Database, rstMain As Recordset
 
'Set some initial properties for the Combo Box (cboUnit)
With Me![cboUnit]
  .RowSourceType = "Value List"
  .ColumnCount = 1
  .ColumnWidth = -1   'Default Width
End With
 
'Will be used in creating a Recordset consisting of Unique Units from tbl_Unit
'Sorted by Units Ascending
strSQL = "SELECT DISTINCT tbl_Unit.Units From Employees ORDER BY tbl_Unit.Units;"
 
'Create a Microsoft Jet Workspace Object with the Default Name, no Password,
'Admin User, using Jet
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
 
'Open a Database Object based on the External Database (Exclusive, Read Only)
Set dbsMain = wrkJet.OpenDatabase(conPathToExternalDB, True, True)
 
Set rstMain = dbsMain.OpenRecordset(strSQL, dbOpenSnapshot)
 
Do While Not rstMain.EOF
  Me![cboUnit].AddItem rstMain![Units]
    rstMain.MoveNext
Loop
 
Don't forget your clean-up chores
rstMain.Close
dbsMain.Close
wrkJet.Close
 
Set rstMain = Nothing
Set dbsMain = Nothing
Set wrkJet = Nothing
 

[mfaisalwarraich]

Okay Mohammad, here we go. Any questions please feel free to ask. The code has been thoroughly tested and is fully functional. Good luck, and let me know how you make out.

Const conPathToExternalDB As String = "\\server\MainDatabase.mdb"
Dim wrkJet As Workspace, strSQL As String
Dim dbsMain As DAO.Database, rstMain As Recordset
 
'Set some initial properties for the Combo Box (cboUnit)
With Me![cboUnit]
  .RowSourceType = "Value List"
  .ColumnCount = 1
  .ColumnWidth = -1   'Default Width
End With
 
'Will be used in creating a Recordset consisting of Unique Units from tbl_Unit
'Sorted by Units Ascending
strSQL = "SELECT DISTINCT tbl_Unit.Units From Employees ORDER BY tbl_Unit.Units;"
 
'Create a Microsoft Jet Workspace Object with the Default Name, no Password,
'Admin User, using Jet
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
 
'Open a Database Object based on the External Database (Exclusive, Read Only)
Set dbsMain = wrkJet.OpenDatabase(conPathToExternalDB, True, True)
 
Set rstMain = dbsMain.OpenRecordset(strSQL, dbOpenSnapshot)
 
Do While Not rstMain.EOF
  Me![cboUnit].AddItem rstMain![Units]
    rstMain.MoveNext
Loop
 
Don't forget your clean-up chores
rstMain.Close
dbsMain.Close
wrkJet.Close
 
Set rstMain = Nothing
Set dbsMain = Nothing
Set wrkJet = Nothing
 

Hi, thank you once again for your generosity. This code is working with some changes, which were mandatory to mode. You just see the code below that you have given to me:

strSQL = "SELECT DISTINCT tbl_Unit.Units From Employees ORDER BY tbl_Unit.Units;"

Now what is Employees???? and when I ran all code there was an error of conflicting distinct with order by. But with some changes i.e. removing Employees and ORDER BY, I got the values into combo box as desired by me. There should be something to do for me also so i did it :)).

The methods you used in all our discussion are really outstanding, which turned Access into Success. Here in my part of World, Pakistan (Asia), there is no particular institute/school, from where u can learn only MS Access but there are a lot about Visual Basic. You just guide me that learning Visual Basic is almost equal to learning MS Access??

Thanking you.

[ADezii]

Hi, thank you once again for your generosity. This code is working with some changes, which were mandatory to mode. You just see the code below that you have given to me:

strSQL = "SELECT DISTINCT tbl_Unit.Units From Employees ORDER BY tbl_Unit.Units;"

Now what is Employees???? and when I ran all code there was an error of conflicting distinct with order by. But with some changes i.e. removing Employees and ORDER BY, I got the values into combo box as desired by me. There should be something to do for me also so i did it :)).

The methods you used in all our discussion are really outstanding, which turned Access into Success. Here in my part of World, Pakistan (Asia), there is no particular institute/school, from where u can learn only MS Access but there are a lot about Visual Basic. You just guide me that learning Visual Basic is almost equal to learning MS Access??

Thanking you.

Now what is Employees???? and when I ran all code there was an error of conflicting distinct with order by.

Employees is a typographical error, it is simply the Table that I do a lot of testing on - substitute tbl_Unit for it.

[mfaisalwarraich]

Hi Again, Here is something for you again :).

I made an unbound form named EntryFrom, which is storing data into a table named MainTable to an Access Database named PatientsAdmission.mdb. This form has a Combo Box named cboCity which is getting values from a table named CityCountryTable of another MS Access Database named CityCountry.mdb.

The cboCity combo box is only populating Cities from Table of CityCountryTable, which has two fields City and Country. Each city has unique id whereas country depends on city. This table does not need ascending/descending order. It depends on entries.

City: Country:

Vancouver Canada
Montreal Canada
Toronto Canada
New York USA
Washington USA
California USA
Birmingham UK
Shanghai China
Delhi India
Mumbai India
Karachi Pakistan
Islamabad Pakistan
Lahore Pakistan
Tokyo Japan

And so on.

Please note that MainTable’s field City is only saving City name from EntryForm’s Combo Box named cboCity and this form is undound.

This form has another ComboBox named cboDisease. This Combo Box is storing values into MainTable’s filed Disease.

Data Type of Fields is Text.

Now, how I can count the number of patients for a particular disease and then for a particular city.

For example I want to count total number of patients in MainTable with Country is equal to Pakistan and Disease is equal to “Tuberclosis”. Whereas MainTable is only saving the name of the city. How I can do all this with some code using Database, Recordset and QueryDefs or TableDefs objects. All three databases using Version 2002. After Calculating the total number of patients for particular disease and country, I want them to display on a new report based on these results.

Thanking you.

(Appologize for any spelling mistake herein above)

Regards

Mohammad Faisal

[ADezii]

Hi Again, Here is something for you again :).

I made an unbound form named EntryFrom, which is storing data into a table named MainTable to an Access Database named PatientsAdmission.mdb. This form has a Combo Box named cboCity which is getting values from a table named CityCountryTable of another MS Access Database named CityCountry.mdb.

The cboCity combo box is only populating Cities from Table of CityCountryTable, which has two fields City and Country. Each city has unique id whereas country depends on city. This table does not need ascending/descending order. It depends on entries.

City: Country:

Vancouver Canada
Montreal Canada
Toronto Canada
New York USA
Washington USA
California USA
Birmingham UK
Shanghai China
Delhi India
Mumbai India
Karachi Pakistan
Islamabad Pakistan
Lahore Pakistan
Tokyo Japan

And so on.

Please note that MainTable’s field City is only saving City name from EntryForm’s Combo Box named cboCity and this form is undound.

This form has another ComboBox named cboDisease. This Combo Box is storing values into MainTable’s filed Disease.

Data Type of Fields is Text.

Now, how I can count the number of patients for a particular disease and then for a particular city.

For example I want to count total number of patients in MainTable with Country is equal to Pakistan and Disease is equal to “Tuberclosis”. Whereas MainTable is only saving the name of the city. How I can do all this with some code using Database, Recordset and QueryDefs or TableDefs objects. All three databases using Version 2002. After Calculating the total number of patients for particular disease and country, I want them to display on a new report based on these results.

Thanking you.

(Appologize for any spelling mistake herein above)

Regards


Mohammad Faisal

Just subscribing, will return later when I have more time.

[mfaisalwarraich]

Just subscribing, will return later when I have more time.

Thank you for your subscription. I am waiting for your reply.

Regards

Mohammad Faisal

[ADezii]

Thank you for your subscription. I am waiting for your reply.

Regards

Mohammad Faisal

It may be a relatively simple matter, but first a couple of critical questions:

1. The External Database, CityCounty.mdb, contains a Table named CityCountyTable, and this Table consists of 2 Fields [City] and [County]. Is this correct?
2. Is the [City] Field in CityCountyTable Unique (no duplication)?
3. MainTable (Local Table) in PatientsAdmission.mdb contains at a minimum [City] and [Disease] Fields. Is this correct?
4. Is the [City] Field in Main Table Unique (no duplication)?
5. Does CityCountyTable have a Primary Key, and if so what is it, and what is its Data Type?
6. Does MainTable have a Primary Key, and if so what is it, and what is its Data Type?
7. Are you familiar with Linking Tables in External Databases?
8. Do you know how to create a Relationship between Tables?
9. Kindly answer these questions as accurately as you can, in order to provide a more accurate solution to your problem.

[mfaisalwarraich]

Thank you for your reply. I would like to reply you as under:

1. No, there is another field of Primary Key ID having Data Type LongInteger (AutoNumber) and New Values set to Increment.

2. Yes, It is unique (no duplication)
3. Yes, mimum these two fields only.
4. No, this field can't be unique in MainTable as many patients can be admitted at the same time belonging to same city.
5. Yes, it has a primary key ID and Data Type is Long Integer (AutoNumber) with New Values set to Increment.
6. Yes, it has a primary key ID and Data Type is Long Integer (AutoNumber) with New Values set to Increment.
7. No, I am not faimiliar with Linking TAbles in External Databases.
8. Yes, I know creating relationships between tables with the help of Relationships in Tools Menu but not familiar doing this with code.


Your such questions are really encouraging and would help me to improve my skills. I have tried my level best to reply your questions accordingly. This forum is really a paradise for people like me around the world. May God bless u all.

Regards

Mohammad Faisal

[ADezii]

Thank you for your reply. I would like to reply you as under:

1. No, there is another field of Primary Key ID having Data Type LongInteger (AutoNumber) and New Values set to Increment.

2. Yes, It is unique (no duplication)
3. Yes, mimum these two fields only.
4. No, this field can't be unique in MainTable as many patients can be admitted at the same time belonging to same city.
5. Yes, it has a primary key ID and Data Type is Long Integer (AutoNumber) with New Values set to Increment.
6. Yes, it has a primary key ID and Data Type is Long Integer (AutoNumber) with New Values set to Increment.
7. No, I am not faimiliar with Linking TAbles in External Databases.
8. Yes, I know creating relationships between tables with the help of Relationships in Tools Menu but not familiar doing this with code.


Your such questions are really encouraging and would help me to improve my skills. I have tried my level best to reply your questions accordingly. This forum is really a paradise for people like me around the world. May God bless u all.

Regards

Mohammad Faisal

1. The first step in the next process will be to create a Link to the CityCountyTable in CityCounty.mdb.
   1. File ==> Get External ==> Link Tables.
   2. Browse to External Database ==> Click on Link Button.
   3. Click on Tables Tab ==> select CityCountyTable ==> OK.
   4. You now have created an External Link to a Table and for all practical purposes, can treat it as a Local Table.
2. Create a Query Joining CityCountyTable and MainTable via the [City] Field Link.
   1. In the Database Window ==> Queries ==> New.
   2. Select Design View ==> OK.
   3. Tables Tab.
   4. Select CityCountyTable ==> OK.
   5. Select MainTable ==> OK.
3. Drag the [City] Field from CityCountyTable to the [City] Field in MainTable and release the Mouse. There should now be a Join between both [City] Fields in the Tables.
4. Right Click the Join Line ------, Join Properties ==> Check the Linkage ==> OK.
5. Close and Save the Query then let me know when and if you have successfully reached this point.

NOTE: Checki the spelling of the [City] Fields in both Tables. If the City Mumbai exists in CityCountyTable but is spelled Mumbia in the MainTable, the Join will be ineffective and the Record(s) will not show.

[ADezii]

Thank you for your reply. I would like to reply you as under:

1. No, there is another field of Primary Key ID having Data Type LongInteger (AutoNumber) and New Values set to Increment.

2. Yes, It is unique (no duplication)
3. Yes, mimum these two fields only.
4. No, this field can't be unique in MainTable as many patients can be admitted at the same time belonging to same city.
5. Yes, it has a primary key ID and Data Type is Long Integer (AutoNumber) with New Values set to Increment.
6. Yes, it has a primary key ID and Data Type is Long Integer (AutoNumber) with New Values set to Increment.
7. No, I am not faimiliar with Linking TAbles in External Databases.
8. Yes, I know creating relationships between tables with the help of Relationships in Tools Menu but not familiar doing this with code.


Your such questions are really encouraging and would help me to improve my skills. I have tried my level best to reply your questions accordingly. This forum is really a paradise for people like me around the world. May God bless u all.

Regards

Mohammad Faisal

1. The first step in the next process will be to create a Link to the CityCountyTable in CityCounty.mdb.
   1. File ==> Get External ==> Link Tables.
   2. Browse to External Database ==> Click on Link Button.
   3. Click on Tables Tab ==> select CityCountyTable ==> OK.
   4. You now have created an External Link to a Table and for all practical purposes, can treat it as a Local Table.
2. Create a Query Joining CityCountyTable and MainTable via the [City] Field Link.
   1. In the Database Window ==> Queries ==> New.
   2. Select Design View ==> OK.
   3. Tables Tab.
   4. Select CityCountyTable ==> OK.
   5. Select MainTable ==> OK.
3. Drag the [City] Field from CityCountyTable to the [City] Field in MainTable and release the Mouse. There should now be a Join between both [City] Fields in the Tables. This Join is a 1 to MANY.
4. Right Click the Join Line ------, Join Properties ==> Check the Linkage ==> OK.
5. Close and Save the Query then let me know when and if you have successfully reached this point.

NOTE: Check the spelling of the [City] Fields in both Tables. If the City Mumbai exists in CityCountyTable but is spelled Mumbia in the MainTable, the Join will be ineffective and the Record(s) will not show.