Solved

Opening another Database?

Posted on 2002-04-10
19
593 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
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 49

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now