Multiple function update

[ronwmon]

Some time ago I was involved in a data conversion between two different automation systems. We were converting DOS information to a SQL database. I was able to execute multiple functions in a single .sql file like:

update dbo.AccountMainTbl0
set SIMSIIRecNbr = SIMSIIRecNbr + 1000000


go

update dbo.AccountMainTbl0
set AccountNumber = left(AccountNumber,2)+substring(AccountNumber,4,2)+'-'+substring(AccountNumber,7,6)


go

update dbo.AccountMainTbl0
set accountnumber = 'PG-'+substring(AccountNumber,6,4)
where substring(AccountNumber,2,1)='p'and substring(AccountNumber,4,1)='g'

Does anybody know how to do this in Access? I don’t want to create and execute multiple updates, would like to execute one Access update file and have it perform multiple tasks.

Later,

Ron--

 

[kjv1611]

From a quick glance at the code you listed, it's pretty much a copy/paste from SQL. The difference will be in how you concatenate things.

Couple differences:
1. in SQL, use single quote, in Access, use double quote.
2. in SQL, use + for concatenation, use & in Access.

Substring, I'm not sure of. But just Google that function for Access to see.

Also, I just found this site today when looking for some references between SQL and Access for something different:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

Let us know how you progress.

 

[ronwmon]

My question concerned multiple functions. For example in the SQL sample what I am calling the first function is:

update dbo.AccountMainTbl0
set SIMSIIRecNbr = SIMSIIRecNbr + 1000000

This is followed by "go" then the next function and so on.

I can't find a way to do this in Access.

Ron--

 

[dhookom]

Access supports only single statements. Multiple sql statements in Access are a "no go".

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

Can somebody point out a reference to SQL Multiple functions? After a quick search, I was getting all sorts of results. Is there a specific word or phrase that would help with this one? I'm just curious.

To me, what your queries look like is you're just updating one field on all records with one query... so maybe I'm just missing something?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PHV]

I'm just missing something
Sure: the OP showed THREE updates in a single flow.

The standard for multiple SQL instructions is to separate them with a semi-colon.

Unfortunately the M$ SQL engines are pretty away from standards ...

In access you have to write macros or VBA code to execute multiple SQL action queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks for clarifying, PHV. I guess I'm just assuming MS SQL Database, but it could be another SQL...

ronwmon,

Can you clarify which SQL you're referring to? Oracle, MySQL, MS SQL, SQLLite, Other?

If the queries will be hitting a SQL Server of any sort, you can just run them as Passthrough queries, and not have to worry about changing much if any of the syntax. I know with my few times of using Oracle, I had to break up any action queries. However when connecting to an MS SQL Server, I can run multiple actions in a single passthrough query.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[ronwmon]

I am using an Access database. I had previous success with multiple action queries in MS-SQL.

I would like to use a multiple action query in Access.

Ron--

 

[PHV]

I would like to use a multiple action query in Access
Again, in Access you have to write macros or VBA code to execute multiple SQL action queries

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

DoCmd.OpenQuery "qryYourQueryNameHere", acViewNormal

You can execute the query with vba as in the example.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[ronwmon]

Thanks to all that have replied, I appreciate your assistance.

Ron--

 

[dhookom]

Making some assumptions about your specifications, your Access vba code might look like:

Dim strSQL1 as String
Dim strSQL2 as String
Dim strSQL3 as String

strSQL1 = "update AccountMainTbl0 set " & _
    "SIMSIIRecNbr = SIMSIIRecNbr + 1000000"

strSQL2 = "update AccountMainTbl0 set " & _
    "AccountNumber = left(AccountNumber,2) & " & _
    "Mid(AccountNumber,4,2) & '-' & Mid(AccountNumber,7,6)"

strSQL3 = "update AccountMainTbl0 set " & _
    "accountnumber = 'PG-' & Mid(AccountNumber,6,4) " & _
    "where Mid(AccountNumber,2,1) = 'p' and Mid(AccountNumber,4,1)='g'"

Currentdb.Execute strSQL1, dbFailOnError
Currentdb.Execute strSQL2, dbFailOnError
Currentdb.Execute strSQL3, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Anyway, why THREE queries for an update in a SINGLE table ?
I'd try this query:

UPDATE AccountMainTbl0 SET
 SIMSIIRecNbr = SIMSIIRecNbr + 1000000
,AccountNumber = IIf(Mid(AccountNumber,2,1)='p' AND Mid(AccountNumber,6,1)='g'
,'PG-' & Mid(AccountNumber,7,4)
,Left(AccountNumber,2) & Mid(AccountNumber,4,2) & '-' & Mid(AccountNumber,7,6))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886