Solved

Opening another Database?

Posted on 2002-04-10
19
620 Views
Last Modified: 2012-06-21
Don't ask why but I need to perform the following routine with 2 databases (db1&db2)

1). From DB1, open DB2 and call function Example()

2). Example() must then close DB1 and perform tasks.

3). When finished, Example() should open DB1 again and    close DB2.

Many Thanks
Jell

P.s. I've tried openaccessproject but it tells me that I've got the database already open?

Any Ideas?


0
Comment
Question by:James Elliott
[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
19 Comments
 

Expert Comment

by:avi_nir
ID: 6930666
hi jell,
the solution don't open and close the database

to access the database use the folowing tech.
write the sql as follows

select * from [<path of the database>].<tablename>

e.g. select * from [C:\temp\DB1].Customer

i think by using the idea ur work will become simple

with regards
avi_nir
0
 
LVL 12

Author Comment

by:James Elliott
ID: 6930694
Its a long story but I HAVE to close the database. Example() runs script that refers to the recordset (amongst other things) of DB1. It won't let me perform the task if DB1 is open.

I'm sure there are ways to fix my existing script but I think this way would be easier.

Thanks

0
 
LVL 52

Expert Comment

by:Ryan Chong
ID: 6930838
Hi,

> 2). Example() must then close DB1 and perform tasks.

Why? Can you edit the Example() function?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 12

Author Comment

by:James Elliott
ID: 6930871
I don't really understand your comment ryancys.

Let me explain: (btw, I'm sort of a beginner!)

There is a large table of data in DB1.
The example() function in DB2 pulls this table from DB1 and (after sorting etc.) distributes the data to various spreadsheets accross the network.

The code I use is something like:

public function Example()

Dim con as New ADODB.Connection
Dim rec as New ADODB.Recordset
Dim str as String

str = "c:\db1.mdb"

con.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & str & ";"

rec.open "SELECT * FROM QRYEXPORT", con

etc.etc.etc

I want to call this procedure from DB1, but it will not perform if db1 is open.

Therefore I want Db1 to open db2, db1 to close, procedure to be called, then db1 opened again.

Thanks

Jell



0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6930952
It doesn't sound as if you have split your database application and tables into a front-end and back-end solution.  If you did this to DB1 and put your tables into DB1data.mdb and just kept your queries, forms, reports, etc in DB1 and just linked to the tables in DB1data.mdb then the DB1data.mdb isn't really open.  You could make you calls to the tables from either the DB1.mdb front-end or the DB2.mdb front-end.  Both could be linked to the same tables back-end database.

If you need more clarification I can elaborate a little more.

Bob Scriver
0
 
LVL 12

Author Comment

by:James Elliott
ID: 6930976
Thanks I understand what you all are saying, and yes I agree that I haven't designed this in the best way but this is a large, messy database I'm talking about. It would take me hours to rearrange it and basically change the structure.

So can someone PLEASE tell me the script to do what I asked in the question.

If it is impossible or just too complicated then say.

Thanks

Jell

0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 6931018
Jell:

In years of working with Access I have never heard of not being able to work with a table in a database that is open.

If this is true:

"I want to call this procedure from DB1, but it will not perform if db1 is open."  Then something is horribly wrong.  This is not how Access works.

Wes

0
 
LVL 12

Author Comment

by:James Elliott
ID: 6931191
I understand and respect that.

As I said I'm a beginner.

I've been trying to put this thing together for weeks and finally it was about to work.

I originally wanted example() in db1 but the database returned that the processes couldn't be performed whilst the database was open.

I am prepared to admit that this is obviously due to shabby coding on my part. This is not an essential database and so I don't care. I also do not really want to spend more hours on this site trying to figure out where I have gone wrong.

If possible PLEASE PLEASE PLEASE

Can I have the code that will open the procedure in DB2.

I have already desinged the forms so that they look nice etc. and all I want PLEASE is the code to do it the way I want to.

Many Thanks in advance.

I don't mean to be abrupt but I have though about the pro's and con's of alternatives but I would just like my question answered if at all possible

Thanks

Jell
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6931434
Is it possible that the database is opened exclusively for a user.  This would cause your problem.  under options, click the advanced tab.  there you will see default open mode. set it to share.  if the database is exclusive then no other user can open it while it's open with that user, make sense?

I have to agree with the others.  Before you try and solve your problem by closing databases and opening them I'd look at solving the bigger problem, why can I not do it the simple way like everyone has suggested.  You should be able to open the database up to 255 times by using the method you're using.  If I were you I'd look at why you can't do just that.  solve that problem, all yourothers will go away.

Failing that.  create a new database and import all the objects into it.  then try your method.

0
 
LVL 12

Author Comment

by:James Elliott
ID: 6931673
I checked that, and the option is set to shared.

The problem is that I'm trying to make ADOBC connection to the current database. (If that makes sense)

It says that I(user) have put the database in a state where it cannot be locked or opened.

I know I have made a grave error but a large amount of script is based around this connection. To change it now MUST be the harder option?

Any suggestions.

Or will someone submit and give me the code to do it my way!!!

Thanks

Jell
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6931697
If I may speak on behave of the others.  the reason we're reluctant to ignore our initial suspisions is because they're serious.  The underlying conserns are, that there is something else wrong and you must look at that first.  One thing I've learned and I'm sure the others can support me on this is that covering up criticle structure problems is a recipee for disaster.  you will be in fact, contributing to a grey matter melt down by ignoring warning signs.

using ADO to open a database by nature does not force this problem that you are speaking of.  spend some time on the MS knowledge base.  Do a search for "ADO", "ado connection", "ADO state"

Also, have you created a new database and imported the objects?

0
 
LVL 3

Accepted Solution

by:
SE081398 earned 100 total points
ID: 6931703
Try this KB article I think you get what you're looking for.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q307640
0
 
LVL 12

Author Comment

by:James Elliott
ID: 6931709
Look.

PLEASE GIVE ME THE CODE

I JUST WANT THE CODE

PLEASE PLEASE PLEASE

I UNDERSTAND ABOUT WHAT YOU ARE SAYING AND BELIEVE ME IF I HAD MORE TIME AND IT WAS AN ESSENTIAL DATABASE THEN FINE, I'LL LOOK INTO MAKING IT WORK PROBABLY. BUT PLEASE, I JUST WANT TO DO IT MY WAY FOR NOW. PLEASE PLEASE PLEASE PLEASE

IF IT MAKES YOU ALL HAPPY I'LL POST ANOTHER QUESTION WITH ALL MY SCRIPT ON SO YOU CAN DISCUSS IT AND FATHOM IT OUT FORM ME. I'LL EVEN OFFER 300 POINTS FOR IT. I PROMISE. BUT PLEASE CAN I JUST HAVE THE CODE REFERRED TO IN THE QUESTION!!!!!!

THANKS


JELL
0
 
LVL 3

Expert Comment

by:SE081398
ID: 6931723
First No need to yell. second; Did you look at the KB Q I posted.  It describes exactly your problem.  There is no code that will solve your problem because the problem is't code it's structure.  I have a sneaky suspision that it's due to not closing multiple recordests and DB objects.  This would cause the system to compound instences of the database and impose the "state" error msg that you're getting.

research on KB first.

good luck.

I'm out.

0
 
LVL 1

Expert Comment

by:dougp23
ID: 6932284
Well, if you just want quick and dirty, to open another DB is pretty simple.

Dim db2 as Database
set db2 = OpenDatabase ("mydata.mdb")  'name of 2nd DB

Once you have transferred over to 2nd DB, you might be able to close the first DB.  Set your warnings to False first, cause if it ain't open, and you go to close it, you'll get an error.

If you can ref the first DB, just:

db.Close    'where db is the Dim of the first DB.

Don't know what you're trying to do, but I hear your frustration, I have been there.  Good luck.
0
 
LVL 12

Author Comment

by:James Elliott
ID: 6933334
Thanks SE. The fix looked promising but didn't work unfortunately. It still came up with the same error.

Dougp23:

How do I call a procedure in DB2 from DB1.

In this pocedure I'll have the code to close DB1

Thanks

0
 
LVL 12

Author Comment

by:James Elliott
ID: 6934050
Thanks all.

Finally figured it out for myself.

Have given the points to SE for effort. Thanks again.

0
 
LVL 3

Expert Comment

by:SE081398
ID: 6934421
Jell, I appreciate the recognition of effort but if my solution didn't solve your problem then there's not need to asign points.  personally I think if you're wanting to give points out for effort I believe everyone here deserves it.  perhaps contact CS and ask if there's a way to reverse the points given to me so you can distribute them to the others as well.  

If you solved the problem we would all like to know what you did.  this way anyone else with a similar problem searching the site, will find your solution.

tell us what you did.

I hope your solution was spawned from comments from at least one of the experts involved.

Glad to hear you got things working.

SE

0
 
LVL 12

Author Comment

by:James Elliott
ID: 6934525
If anybody else is disgruntled then I will contact CS. Otherwise, I thought you were the most help to me. Thanks
I didn't solve the problem, I got around it unknowingly.

As you know, I was trying to run some ADO script that pulled information from a number of tables/queries in my database and then distribute the data(once sorted) to many spreadsheets across out network. The code worked fine from outside the database but when I tried to move the module inside the database, it would not work. Giving the error "The database has been put in a state by user where it cannot be locked or changed."

I therefore can up with the plan to leave the procedure outside the database, but to call it from the database.

Whilst playing around, I found the following code worked without problem.

Project.db1.mdb = First Database (where I wanted the procedure to be ketp!)
Billing.mdb = second database (where the procedure is kept)

PLEASE EXCUSE MY DiSGRACEFULL SCRIPTING AND LACK OF PARAMETERS AND LOOPS!!!! - Enjoy!

>>>>>>>>>>>>>>>>>>>>>>>>>>Project containted the following code>>>>>>>>>>>>>

Private Sub Command1_Click()
Dim appAccess As Access.Application
    Const strConPathToSamples = "u:\database\billing module\billing.mdb"
    strDB = strConPathToSamples & "billing.mdb"
    Set appAccess = _
        CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strConPathToSamples
    appAccess.DoCmd.RunMacro "james"
End Sub

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>This procedure was then initiated by the above macro (Billing.mdb)>>>>>>>

Screen.MousePointer = vbhourglass
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlapp As Object
Dim xlwb As Object
Dim xlws1 As Object
Dim recarray As Variant
Dim strDB As String
Dim fldcount As Integer
Dim reccount As Long
Dim iCol As Integer
Dim iRow As Integer

strDB = "u:\database\billing module\project.db1.mdb"

cnt.Open "Provider=Microsoft.jet.oledb.4.0;" & _
"Data Source=" & strDB & ";"
Set xlapp = CreateObject("Excel.Application")
''''''''''''''''''''''''''''''''''''''''''' Employee 1


Set xlwb = xlapp.Workbooks.Open("u:\database\billing module\Standard_Format.xlt")

rst.Open "Select * From BillingQ WHERE [F3] = '11'", cnt
Set xlws1 = xlwb.Worksheets("Adrian")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '12'", cnt
Set xlws1 = xlwb.Worksheets("Charles")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '13'", cnt
Set xlws1 = xlwb.Worksheets("Michael")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

Set xlws1 = Nothing
Set xlwb = Nothing
xlapp.ActiveWorkbook.SaveAs Filename:="u:\billing sheets\AH.xls"
xlapp.ActiveWorkbook.Close

''''''''''''''''''''''''''EMployee 2


Set xlwb = xlapp.Workbooks.Open("u:\database\billing module\Standard_Format.xlt")

rst.Open "Select * From BillingQ WHERE [F3] = '16'", cnt
Set xlws1 = xlwb.Worksheets("Adrian")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '17'", cnt
Set xlws1 = xlwb.Worksheets("Charles")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '18'", cnt
Set xlws1 = xlwb.Worksheets("Michael")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

Set xlws1 = Nothing
Set xlwb = Nothing

xlapp.ActiveWorkbook.SaveAs Filename:="u:\billing sheets\WN.xls"
xlapp.ActiveWorkbook.Close
'''''''''''''''''''''''''''''''''''''''''''' Employee 3

Set xlwb = xlapp.Workbooks.Open("u:\database\billing module\Standard_Format.xlt")

rst.Open "Select * From BillingQ WHERE [F3] = '6'", cnt
Set xlws1 = xlwb.Worksheets("Adrian")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '7'", cnt
Set xlws1 = xlwb.Worksheets("Charles")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '8'", cnt
Set xlws1 = xlwb.Worksheets("Michael")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

Set xlws1 = Nothing
Set xlwb = Nothing

xlapp.ActiveWorkbook.SaveAs Filename:="u:\billing sheets\JG.xls"
xlapp.ActiveWorkbook.Close

'''''''''''''''''''''''''''''''''''''''''' Employee 4


Set xlwb = xlapp.Workbooks.Open("u:\database\billing module\Standard_Format.xlt")

rst.Open "Select * From BillingQ WHERE [F3] = '21'", cnt
Set xlws1 = xlwb.Worksheets("Adrian")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '22'", cnt
Set xlws1 = xlwb.Worksheets("Charles")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '23'", cnt
Set xlws1 = xlwb.Worksheets("Michael")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

Set xlws1 = Nothing
Set xlwb = Nothing

xlapp.ActiveWorkbook.SaveAs Filename:="u:\billing sheets\CH.xls"
xlapp.ActiveWorkbook.Close

''''''''''''''''''''''''''''''''''''''''''' Employee 5


Set xlwb = xlapp.Workbooks.Open("u:\database\billing module\Standard_Format.xlt")

rst.Open "Select * From BillingQ WHERE [F3] = '1'", cnt
Set xlws1 = xlwb.Worksheets("Adrian")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '2'", cnt
Set xlws1 = xlwb.Worksheets("Charles")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '3'", cnt
Set xlws1 = xlwb.Worksheets("Michael")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

Set xlws1 = Nothing
Set xlwb = Nothing

xlapp.ActiveWorkbook.SaveAs Filename:="u:\billing sheets\HB.xls"
xlapp.ActiveWorkbook.Close

'''''''''''''''''''''''''''''''''''''''''' Employee 6

Set xlwb = xlapp.Workbooks.Open("u:\database\billing module\Standard_Format.xlt")

rst.Open "Select * From BillingQ WHERE [F3] = '96'", cnt
Set xlws1 = xlwb.Worksheets("Adrian")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '97'", cnt
Set xlws1 = xlwb.Worksheets("Charles")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

rst.Open "Select * From BillingQ WHERE [F3] = '98'", cnt
Set xlws1 = xlwb.Worksheets("Michael")
xlapp.Visible = True
xlapp.UserControl = True
xlws1.Cells(5, 1).CopyFromRecordset rst
rst.Close

cnt.Close
Set rst = Nothing
Set cnt = Nothing
Set xlws1 = Nothing
Set xlwb = Nothing

xlapp.ActiveWorkbook.SaveAs Filename:="u:\billing sheets\MM.xls"
xlapp.Quit
Set xlapp = Nothing
Screen.MousePointer = vbarrow

'''''''''''''''''''''''''''''''''''' Send Mail
Dim ol As New Outlook.Application
Dim olMail As Outlook.MailItem
Dim strSubject As String
Dim strMSG As String

strSubject = "Billing Sheets Ready for Completion"
strMSG = "Dear Staff" & vbCrLf & vbCrLf
strMSG = strMSG & "This is an automated E-Mail from the client database to say that the Billing Sheets are ready for completion and can be found in u:\billing sheets\"

 Set olMail = ol.CreateItem(olMailItem)

 With olMail
     .To = "James Elliott"
     '.Attachments.Add "c:\sometextfile.txt"
     '.Attachments.Add "c:\anothertextfile.txt"
     .Subject = strSubject
     .Body = strMSG
     '.Display
     .Send
 End With

 Set olMail = Nothing
 Set ol = Nothing

Set rst = Nothing
Set dbs = Nothing

MsgBox "Billing Sheets completed and E-Mails sent to all relevant staff"

END

Thanks


JELL

0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

729 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