My first ADO, need some clarification 1

[sunmorgus]

Hello all! I have a question about ADO, as I am entirely new to it and want to be able to utilize it in my database (I would like to eventually get rid of linked tables, but at this point I feel like I am dreaming! Hehe!). I am currently using DAO, and seem to have a hold on it, and have coded my first ADO procedure, but I need a little clarification on something. First off, here is my code:

Private Sub Form_Load()
On Error GoTo HandleErrors

Dim snSQL As String

'open connection to QC database
Set adSerialConn = New ADODB.Connection
adSerialConn.Open "Provider='Microsoft.JET.OLEDB.4.0';" & _
                    "Data Source='T:\Quality Database\SerialNumber.mdb'"

'build the recordsource for the macola info subform
snSQL = "SELECT SERIALNO.SERIAL_NO, SERIALNO.SUBASSY, SERIALNO.DATE, SERIALNO.CUST_NAME, SERIALNO.SHIPDATE" & _
        " FROM SERIALNO " & _
        "WHERE SERIALNO.SERIAL_NO = " & Nz(Forms![Serial Number Manual Update Form]![Serial Number], "") & ";"

'apply the recordsource
Set adSerialRS = New ADODB.Recordset
adSerialRS.Open snSQL, adSerialConn, adOpenDynamic, adLockReadOnly

With adSerialRS
        Me.SERIAL_NO.SetFocus
        Me.SERIAL_NO.Text = Nz(.Fields("SERIAL_NO"), "")
        Me.Date.SetFocus
        Me.Date.Text = Nz(.Fields("DATE"), "")
        Me.SUBASSY.SetFocus
        Me.SUBASSY.Text = Nz(.Fields("SUBASSY"), "")
        Me.CUST_NAME.SetFocus
        Me.CUST_NAME.Text = Nz(.Fields("CUST_NAME"), "")
        Me.SHIPDATE.SetFocus
        Me.SHIPDATE.Text = Nz(.Fields("SHIPDATE"), "")
        Forms![Serial Number Manual Update Form]![Serial Number].SetFocus
End With

err_exit:
    Exit Sub
HandleErrors:
    Me.CUST_NAME.SetFocus
    Me.CUST_NAME.Text = "No Matching Records."
    Resume err_exit
    
End Sub

What I need to know is, is there a way to just set the recordsource of the form to the ensuing recordset I get from ADO, instead of having to fill each field manually, like I do in this section:

With adSerialRS
        Me.SERIAL_NO.SetFocus
        Me.SERIAL_NO.Text = Nz(.Fields("SERIAL_NO"), "")
        Me.Date.SetFocus
        Me.Date.Text = Nz(.Fields("DATE"), "")
        Me.SUBASSY.SetFocus
        Me.SUBASSY.Text = Nz(.Fields("SUBASSY"), "")
        Me.CUST_NAME.SetFocus
        Me.CUST_NAME.Text = Nz(.Fields("CUST_NAME"), "")
        Me.SHIPDATE.SetFocus
        Me.SHIPDATE.Text = Nz(.Fields("SHIPDATE"), "")
        Forms![Serial Number Manual Update Form]![Serial Number].SetFocus
End With

Sorry for the long post, and thanks in advance for any help!

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/281998/EN-US/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

Thanks! That is it exactly!!!!

 

[sunmorgus]

Ok, I have another question now. How would I go about using 2 connections in one recordset? I have an excel worksheet that I want to use to update a table in my backend db, I set a connection for both the excel table and the backend db, but the .open command can't see both connections. Is there a way to do this?

 

[PHV]

Play with 2 connections and 2 recordsets.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

Ok, here is my code:

Dim adMPLLinkConn As ADODB.Connection 'Master Price List
Dim adMPLConn As ADODB.Connection 'Database
Dim adMPLDeleteRS, adMPLUpdateRS As ADODB.Recordset
Dim mplDeleteSQL, mplUpdateSQL As String

'open the connections to the database tables and master price list
Set adMPLLinkConn = New ADODB.Connection
Set adMPLConn = New ADODB.Connection

adMPLLinkConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=T:\Tech Support\MasterPriceList.xls;" & _
                    "Extended Properties='Excel 8.0;HDR=Yes';Persist Security Info=False"
adMPLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Nick DeMayo;" & _
                "Data Source=R:\My Documents\Test Database\Test Backup\Test Tables.mdb;" & _
                "Mode=ReadWrite|Share Deny None;Persist Security Info=False;" & _
                "Jet OLEDB:System database=t:\Database Project\Secured.mdw", "Nick DeMayo", "Nic"

'set SQL Statements
mplDeleteSQL = "DELETE [Part Number], [Description A], [Description B], [Selling Price], " & _
                "[Current Cost], [Devlin $$], [Use On Code], [F8] " & _
                "FROM [Master Price List];"
mplUpdateSQL = "INSERT INTO [Master Price List]([Part Number], [Description A], [Description B], [Selling Price], " & _
                "[Current Cost], [Devlin $$], [Use On Code], [F8]) " & _
                "SELECT * FROM [Price List$];"


'process master price list
Set adMPLDeleteRS = New ADODB.Recordset
Set adMPLUpdateRS = New ADODB.Recordset
adMPLDeleteRS.Open mplDeleteSQL, adMPLConn
adMPLUpdateRS.Open mplUpdateSQL, adMPLLinkConn

I get an error message "The Microsoft Jet database engine could not fine the object 'Master Price List'" on the adMPLUpdateRS.open line. what am I doing wrong?

 

[PHV]

Just a thought: why not simply use a QueryTable in MasterPriceList.xls ?
when in excel menu Data -> External data -> Create query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

That would probably work, however, my database uses a .mdw file for security, and the excel worksheet can't access it...

 

[PHV]

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw;Uid=myname;Pwd=mypassword;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

Pardon me for my stupidity, but where do I use that?

 

[PHV]

This is a connection string example for a secured database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

That I have actually accomplished in my code above.

adMPLConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Nick DeMayo;" & _
                "Data Source=R:\My Documents\Test Database\Test Backup\Test Tables.mdb;" & _
                "Mode=ReadWrite|Share Deny None;Persist Security Info=False;" & _
                "Jet OLEDB:System database=t:\Database Project\Secured.mdw", "Nick DeMayo", "Nic"

I changed my update sql statement to read as such (notice the 'IN' section:

mplUpdateSQL = "INSERT INTO [Master Price List]([Part Number], [Description A], [Description B], [Selling Price], " & _
                "[Current Cost], [Devlin $$], [Use On Code], [F8]) IN 'r:\My Documents\Test Database\Test Backup\Test Tables.mdb' " & _
                "SELECT * FROM [Price List$];"

This sql statement works fine from within the query design view, but when I run it from code I get the same 'no permission to access this database' error. Why would it work in design view, and not from code?

 

[PHV]

It was a reply to the excel worksheet can't access it...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886