Using ADODC & ACCESS 2000, want to copy table. 3

[THOMASNG]

I have successfully used ADODC and DataGrid to create a
VB6 form an ".mde" file that is used to update (partially) the original inventory stored on an ACCESS 2000 database/table.
I see that by using the ACCESS 2000 standard "Find Unmatched" and "Find Duplicate" queries, I can use this .mde table update the origianal database/table. However, I can't remember/figure out how to specify values that belong to the .mde table.

 

[CCLINT]

conn.Execute "UPDATE TheTable SET SomeTextField='ABC', SomeNumberField=123"

conn.Execute "INSERT INTO TheTable(SomeTextField, SomeNumberField) VALUES('ABC',123)"

conn.Execute "INSERT INTO TheTable(SomeTextField, SomeNumberField) SELECT SomeTextField,SomeNumberField FROM SomeOtherTable WHERE SomeTextField='SomeCriteria'"

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[THOMASNG]

Gee, I can't seem to find any online guidance on "conn"!

Simplifying what I want to do:
1) Append the data table made by ADODC to the
pre-existing data table made by ACCESS 2000.
2) Scan this new table for any duplicates, deleting
those with the earlier entry date. (The entry date
is aleady one of the fields of the data table.)

 

[johnwm]

conn is the variable name that CCLINT is using to refer to his (already established) Connection object.

There are some good examples of how to set up all sorts of ADO connections here:

http://www.able-consulting.com/ADO_Conn.htm

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[CCLINT]

You can also do this:

Dim conn As ADODB.Connection

Set conn = ADODC1.Recordset.ActiveConnection

and then use the "Conn" object as above.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[THOMASNG]

I'm getting closer!

The code I added, to try to establish a connection to
my original data-table is:

In Form|Activate:

Dim str_Conn As String
Dim cnn1 As ADODB.Connection

' ******************

str_Conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\VBA Practice Files\CCRTable1; _
Initial Catalog=pubs;User ID=sa;Passwod=; "
Set cnn1 = New ADODB.Connection
cnn1.Open str_Conn

At least it now acknowledges I correctly accessed the
same Provider as before. However. now I get the message:

"Run-time error '-214721887 (80040e21)'"

"Multi-step OLE DB operation generated errors. Check
each OLE DB status value, if available. No work done."

I'm not quite sure what they want here.

 

[THOMASNG]

Can somebody help here/ I seem so slose.

 

[THOMASNG]

Can somebody help here/ I seem so close.

 

[DrJavaJoe]

Try this connection string.

str_Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBA Practice Files\CCRTable1;Persist Security Info=False"

 

[THOMASNG]

Even better, I think. The error message has changed to:

'Run-time error '-2147467259(80004005)'

'C:\VBA Practice Files\CCRTable1' is not a valid path.
Make sure that the pathname is spelled correctly and that
you are connected to the server on which the file resides.

I'm going to verify that there are no mistakes here, but
I recall both that pathname and server connection as being OK.

 

[THOMASNG]

I've corrected the pathname to : 'C:\Access VBA Practice Files\CCRTable1', but got the same error message is before.

 

[DrJavaJoe]

Shouldn't CCRTable1 have an extension?

 

[THOMASNG]

I modified the pathname to inclde Access VBA Practice
Files, but no improvement. I guessed that the extension to "CRTable1" would be ".tbl", but again no improvement.
Then tried commenting out the line:
"cnn1.Open str_Conn"
and (for some reason) that stopped the error messages,
but connected me to the .mde table, not the original ACCESS 2000 table.

 

[CCLINT]

Just use:
str_Conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VBA Practice Files\CCRTable1.MDB" [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[THOMASNG]

I changed the pathname to include ".MDB". No improvement,
exactly same results as before.

 

[DrJavaJoe]

Add a Microsoft ADO Data Control to your form then right click on it and select properties from the drop down menu. Thenclick the Build -> Select Jet 4.0 -> Next -> Browse to your DB -> Test Connection -> OK. Now copy the Connection string and set str_Conn = to it.

 

[THOMASNG]

Yup, that did it. Had to modify the connection via the
ADODC Properties table, not just in code. All that assisted me, have a star.

 

[DrJavaJoe]

 

[THOMASNG]

He's speechless!