Updating Access Tables from Excel VBA

[jonsan]

I need to update multiple Access tables within the same database from an Excel Macro. I am not sure which ADO object to use, or the correct syntax. I am familiar with SQL, and already have the Update Query written. Can anyone help? Do I need to provide more/different information? Thanks!

Jonsan

 

[RoyVidar]

Open a connection to the database.

[tt]dim cn as adodb.connection
set cn=new adodb.connection
cn.connectionstring="provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = <path and name of db>;"
cn.open[/tt]

More connection strings can be found at Able Consulting

If the queries you've written are VBA SQL strings, you should be able to run them like this:

[tt]cn.execute strSql,,adcmdtext[/tt]

If they are stored queries within Access, I think you should be able to reach them thru

[tt]cn.execute "qryMyInsert",,adcmdstoredproc[/tt]

if they do not contain any parameters. Should they contain parameters, you'll need use of the command object.

Roy-Vidar

 

[SkipVought]

hi,

You need a Connection ADODB object and a Recordset ADODB.

Have you set a reference in the Excel VB Editor to the ActiveX data Objects x.y Library? This will expose the ADODB objects.

Use an UPDATE SQL

samples...

Sub getConnectString()
    Dim cnDB As ADODB.Connection
    
    Set cnDB = New ADODB.Connection
    
    With cnDB
        .Properties("Prompt") = adPromptAlways
        .Open
    End With
    
    [A1] = cnDB.ConnectionString
    
    cnDB.Close
    Set cnDB = Nothing
End Sub
Sub AccessData()
    Dim cnDB As ADODB.Connection, rsTable As ADODB.Recordset
    
    Set cnDB = New ADODB.Connection
    
    With cnDB
        .Properties("Prompt") = adPromptNever
        .Open [A1]
    End With
    
    Set rsTable = New ADODB.Recordset
    rsTable.Open "Select * FROM FPRPTSAR.MC_CC ", cnDB, adOpenForwardOnly
    
    With rsTable
        If Not .BOF Or .EOF Then
            .MoveFirst
            Do While Not .EOF
                MsgBox rsTable("CC")
                .MoveNext
            Loop
        End If
    End With
    
    rsTable.Close
    Set rsTable = Nothing
    cnDB.Close
    Set cnDB = Nothing
End Sub

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[jonsan]

Thanks! Exactly what I was looking for.

Jonsan

 

[jonsan]

Okay, here is my code:

<code>
Sub RemoveFax(sAreaCode As String, sFaxNum As String)

Dim cConn As ADODB.Connection
Dim sSQL As String, sConn As String, sDB As String
Dim lCount As Long
Dim sFaxRem As String

sFaxRem = sAreaCode & sFaxNum

'Instantiate the ADO-objects.
Set cConn = New ADODB.Connection

'Path to the database.
sDB = *deleted for public posting*

'Create the connectionstring.
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & sDB & ";"

cConn.Open sConn

'The 1st raw SQL-statement to be executed.
sSQL = "UPDATE RemoveFaxTest SET BusinessFax = '000" & sFaxNum
sSQL = sSQL & "' WHERE BusinessFax = sFaxRem"

cConn.Execute sSQL, lCount, adCmdText
MsgBox "Removed " & lCount & " entries from fax lists."

'Release objects from the memory.
cConn.Close
Set cConn = Nothing

End Sub

</code>

I am receiving an error saying that there was no value given for one or more required parameters on the execute line. Any thoughts? I have the 2.7 ADO reference checked, and the database is Access2000.

Thanks!

J

PS I am not sure how to format code in this forum site.

 

[RoyVidar]

I think the criterion:

[tt]sSQL = "UPDATE RemoveFaxTest SET BusinessFax = '000" & sFaxNum
sSQL = sSQL & "' WHERE BusinessFax = '" & sFaxRem & "'"[/tt]

Since it's declared as a string, I'm guessing it is a string -> single quotes, and as above, it needs to be concatenated into the sql string.

Roy-Vidar

 

[jonsan]

Oops. Just me being a dolt and rushing to the forum too quick!

Thanks!

Jonsan

 

[jonsan]

Okay, next issue here:
I need to perform the Update Query on multiple tables in the same DB. Should I just use multiple execute statements with the same Connection object, or is there a way to write the Update SQL so it performs the update in multiple tables?

Thanks!

J

 

[RoyVidar]

As you would do in Access, probably execute one at a time, using the same connection.

[tt]cn.execute strsql1,,adcmdtext
cn.execute strsql2,,adcmdtext
...[/tt]

For info on formatting, check out the "process TGML" link in the bottom of the reply window.

Roy-Vidar