ADO help!

[badgers]

Martin had helped me with this using DAO but it does not work with ADO
I am using ADO and a MS datagrid.
I can get my grid to display any table I want when ever I want. which is great.
Now here is my problem. How do I copy "table2" in "c:\db1.mdb" to "hello" in "c:\db1.mdb"?
I want table2 untouched so renaming the thing wont work.
Martin with the ADO referenced I get an error about invalid use of the new keyword when used as
Set Fld = New Field
(I had DAO reference on when I tried this, somehow ADO reference screw up you nice sub)
Can ADO do what I need?
thank you for your time and have a good day

[Thai]

assuming you have a connection to the db, lets call it cn, this should work, I haven't done this for a while so bare with me.

cn.execute "INSERT INTO Hello SELECT * FROM Table2"

If the fields match exactly.. should duplicate the table, the Hello table must exist first though.

Thai

[badgers]

that is the problem the table does not exist. I need create the table.
Can ADO make a table. I am using Access 97
I am using ADO because I need the MS datagrid.
any ideas?

[badgers]

here is a thought!
how do I set up something using the access 8.0 reference.
There is a VBA style command in access called
docmd.copyobject
the following is an access made macro
How can I use this in my VB program?
early binding for access stumps me

Function Macro1()
On Error GoTo Macro1_Err

    ' test
    DoCmd.CopyObject "", "Hello", acTable, "Table2"


Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

End Function

[JazzBass]

Badgers,
Hi. I'm not really good with Databases, but here is a query that creates the table for you. Maybe you can run the query you had before (Insert Into...) and if you get an error, then use a error handler to capture it at perform the following query:

SELECT * INTO HELLO FROM TABLE2;

That is a query I wrote in Access 97. Other syntax you might want to try is:

CREATE HELLO AS SELECT * FROM TABLE2;

Hope that helps at least a little. Sorry for not being too well versed in databases.

JazzBass

[badgers]

did you say your not good with databases?! HA
I just don't get this stuff.
My simple example has the following code:

Option Explicit
Private Sub Command1_Click()
  'simple test if I can change the info shown by the datagrid
  Adodc1.RecordSource = "select * from table1" 'show table 1
  Adodc1.Refresh
  DataGrid1.Refresh
End Sub
Sub copy(TarBase As String, FrName As String, ToName As String)
  'set up as a sub to allow code reuse
  Adodc1.RecordSource = "CREATE HELLO AS SELECT * FROM TABLE2"
End Sub
Private Sub Command2_Click() 'call sub
  Call copy("c:\test\db1.mdb", "table2", "HELLO")
End Sub
Private Sub Command3_Click()
  'now try to show the new table in the datagrid
  Adodc1.RecordSource = "select * from HELLO"
  Adodc1.Refresh
  DataGrid1.Refresh
End Sub

A am a putz

[badgers]

This is an interesting problem
new standard exe with one form and one command button
reference MS access 8.0

Option Explicit

Private Sub Command1_Click()
Call copy("c:\test\db1.mdb", "table2", "HELLO")
End Sub
Sub copy(TarBase As String, FrName As String, ToName As String)
  'set up as a sub to allow code reuse
  Dim ObjAccess As Access.Application
  Set ObjAccess = New Access.Application
  Call ObjAccess.DoCmd.CopyObject(TarBase, ToName, acTable, FrName)
End Sub

I get the following run time error:
Run-time error '2486':

You can't carry out this action at the present time.@You tried to run a macro or used to DoCmd object in Visual Basic to carry out an action. However, Microsoft Access is performing another activity that prevents this action from being carried out now.
For example, no actions on a form can be carried out while Microsoft Access is repaining a control or calculating an expression.@Carry out the action later.@1@@1

I then get to choose end or debug, continue and help are grayed out and the @ and 1 are in the message even though they make little sense to me
thank you for your time and have a good day

[badgers]

never mind about copying the table. I am stupid and I finaly realized how do do something so simple. Maybe not the fastest performer but just a few simple lines of code.

I have this thing doing what I want Kind of.....
I get a window poping up with the following message.
[ADODC]: no RecordSource specified, [ADO]:No command has been set for the command object.
I get an OK command button.
when I click OK things work fine.
here is the code:

Private Sub LoadNewDoc()
    'table2 is a template table. each child form should point
    'to a new table with a name=to the caption of the child form
    Dim frmD As frmDocument
    Dim StrTemp As String 'name of panel
    Set frmD = New frmDocument 'create a child form based on the model
    StrTemp = InputBox("enter Panel Name", "Panel Name?") 
'Get name for this panel
    frmD.Caption = StrTemp 
'set child window caption to the panel name
    Call copy("c:\test\db1.mdb", "table2", StrTemp) 
'copies table2 to strtemp's value
    frmD.Adodc1.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0
;Data Source=C:\test\db1.mdb;Mode=Read|Write|Share 
Deny None;Persist Security Info=False"
    frmD.Adodc1.RecordSource = "select * from " & StrTemp
    frmD.DataGrid1.Caption = StrTemp
    frmD.Show 'show the user what they've won!
    frmD.Adodc1.Refresh
    frmD.DataGrid1.Refresh
End Sub
Sub copy(TarBase As String, FrName As String, ToName As String)
  'set up as a sub to allow code reuse
  Dim ObjAccess As Access.Application
  Set ObjAccess = New Access.Application
  ObjAccess.OpenCurrentDatabase (TarBase)
  Call ObjAccess.DoCmd.CopyObject(TarBase, ToName, acTable, FrName)
  ObjAccess.CloseCurrentDatabase
  Set ObjAccess = Nothing
End Sub

the obvious question is Why is that message popping up?
it really does what I want it to do. The final code wont have a hardcoded path for the database. I used them just for testing purposes. Also the strtemp names thus far have no spaces in them. I will have to figure out how to deal with them later.
thanks to those who have given me their help.
and thanks in advance to anyone who knows how to fix my current situation.

[Edited by badgers on 05-25-2000 at 05:57 PM]

[Edited by badgers on 05-25-2000 at 06:43 PM]

[badgers]

HA I figured it out!
well not everything yet.
I had set the connection string at design time and that was what was causing the error. deleted the connection string from the properties window and boom works(kind of)
I need to handle spaces in the "Select * from " & StrTemp
StrTemp="Panel M"
freaks my program out
StrTemp="is"
freaks my program out
the "is" ain't no problem, but every panel name has a space.
I have to be able to handle spaces and any other wild characters that freak out SQL
thank you all for your time and have a good day
sorry about the wide screen format this post is taking