 | |
Archiving/restoring 'deleted' records
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: Archiving/restoring 'deleted' records -
09-29-2010
, 07:20 AM
|
Jim Devenish <internet.shopp... (AT) foobox (DOT) com> wrote innews:90cce6bf-ae85-4212-b15a-f41b30075925 (AT) w19g2000yqb (DOT) googlegroups.co m: On Sep 27, 6:22*pm, "David W. Fenton" NoEm... (AT) SeeSignature (DOT) invalid> wrote: Jim Devenish <internet.shopp... (AT) foobox (DOT) com> wrote innews:e8fe58a4-a401-48 d2-aeb6-74c92cbd3... (AT) k10g2000yqa (DOT) googlegroups.co m: My problem is now: Is it possible, and if so how, to execute a procedure in the back end from the front end? I can open the back end database in the front end: * * Dim theDatabase As DAO.Database * * Set theDatabase = OpenDatabase(theServerFileName) but can I then run a procedure in it? You have to change the connection object to point to the back end database instead of to the current database (CurrentProject.Connection). That would mean that you'd change this: * Set cat.ActiveConnection = CurrentProject.Connection ...to this: * Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ * * * "Data Source=" & strDBPath ...where strDBPath is the name of the back end database as returned by CurrentDB.Name when called in the back end database (or Mid(CurrentDB.TableDefs("MyLinkedTable").Connect, 11)). I'd also suggest using late binding throughout in Allen's code so that you don't have to add an ADO reference. That would entail these changes to the declarations: * Dim cat As Object * 'Catalog of current project. * Dim tbl As Object * 'Each table. * Dim col As Object * 'Each field ...and you'd add this line before Set cat.ActiveConnection: * Set cat = CreateObject("ADOX.Catalog") ...and that's it, so far as I can see. I did check to see if this could be done in DAO, but it appears that the Seed property of an Autonumber column is simply not visible via DAO. This is highly annoying that 10 years after the property was introduced, only a non-native data interface library can be used to manipulate this very important property. I tested in A2007 to see if that had changed, but it has not. I didn't bother to check in A2010, though. -- David W. Fenton * * * * * * * * *http://www.dfenton.com / contact via website only * *http://www.dfenton.com/DFA/ David Thanks for this. *It looks most promising. *I am however having a problem with the line: Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ * * * "Data Source=" & strDBPath I get a runtime error "Object required" I feel that the present right hand side should be a parameter to an new Connection object but cannot see how to create it. I have tried the following without success: * * *CurrentProject.OpenConnection * * *"Provider=Microsoft.Jet.OLEDB.4.0;" & _ * * * * * * * * * * * * * * * * * ** * * * * * * * "Data Source=" * * * * * * * * * * * * * * * & getServerFileName * * *Set cat.ActiveConnection = CurrentProject.Connection and at the end resetting the connection: * * CurrentProject.CloseConnection * * CurrentProject.OpenConnection but the OpenConnection gives rise to an error: "the expression you entered refers to an object that is closed or does not exist" The pathname is correct by the way. Any help would be appreciated Did you do what I said about late binding or not? Based on the documentation I looked up before posting that, this should work: * Dim cat As Object * 'Catalog of current project. * Dim tbl As Object * 'Each table. * Dim col As Object * 'Each field * Set cat = CreateObject("ADOX.Catalog") * Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ * * * "Data Source=" & strDBPath There is no need to also have a connection object defined and assign that, according to the documentation I found. -- David W. Fenton * * * * * * * * *http://www.dfenton.com/ contact via website only * *http://www.dfenton.com/DFA/ |
I did try with late binding but the following failed:
Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
* * * "Data Source=" & strDBPath
However when I followed Bob's advice and removed the Set:
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
* * * "Data Source=" & strDBPath
it worked.
I'll leave you and Bob to discuss the documentation and overloading.
Jim
#22
| |||
| |||
|
|
I did try with late binding but the following failed: Set cat = CreateObject("ADOX.Catalog") Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & strDBPath However when I followed Bob's advice and removed the Set: Set cat = CreateObject("ADOX.Catalog") |
hopefully you will see that I was advising against eliminating the Set
keyword.
--
HTH,
Bob Barrows
#23
| |||
| |||
|
|
Jim Devenish wrote: I did try with late binding but the following failed: *Set cat = CreateObject("ADOX.Catalog") *Set cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" &_ * "Data Source=" & strDBPath However when I followed Bob's advice and removed the Set: *Set cat = CreateObject("ADOX.Catalog") Ooh, that wasn't advice: it was explanation. If you reread what I wrote, hopefully you will see that I was advising against eliminating the Set keyword. -- HTH, Bob Barrows |
I was simply trying it out to compare it with David's suggestion, in
answer to his question about whether I had used late binding.
In my real code, I did follow your advice!!
#24
| |||
| |||
|
|
Sorry Bob. I should not have used the word 'advice' in this context. I was simply trying it out to compare it with David's suggestion, in answer to his question about whether I had used late binding. In my real code, I did follow your advice!! |
documentation, and it was this:
http://msdn.microsoft.com/en-us/libr...8VS.85%29.aspx
....and that quite clearly opens a connection object and uses that to
assign the ActiveConnection. I am unclear on where I got the idea
that you could do the latter directly with a proper connect string.
Actually, I did some more digging, and here's where I found that:
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx
....the relevant code there looks like this:
Dim catDB As ADOX.Catalog
Dim cmd As ADODB.Command
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath
I just converted that to late binding, thus:
Dim catDB As Object
Dim cmd As Object
Set catDB = CreateObject("ADOX.Catalog”)
' Open the catalog.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDBPath
....which I’d think is completely equivalent.
But I didn’t test it myself, as I don’t do ADO nor muck up my
databases with ADO references. I should have sat on my hands, I
guess.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
#25
| |||
| |||
|
|
David W. Fenton wrote: I checked the documentation on that and it isn't required to do that, so far as I could tell from what it said. It is when you use the Set keyword. The Set keyword requires an object (or Nothing) to be assigned to the variable or property in the statement. |
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx
....and added in the SET command. So I started with correct code and
“fixed” it!
I’m glad that you’ve cleared that up.
--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.