Transactions in Excel and SQL Insert

[ianohlander]

Hey again,

2 questions:

A general sort:

I have a number of SQL statements that I need to execute from a VBA subroutine. I have 3 tables: customerTable, roomTable, and ReservationTable. customerTable has numerous fields, including customerIDNumber as key. reservationTable has numerous fields, including reservationIDNumber (as key), customerIDNumber, and roomIDNumber. Finally, roomTable has numerous fields including roomIDNumber and reservationIDNumber (the last is for checking availability- if it contains the default -1, then it is free).

Now, I have to to basically accomplish this:

• get a room from roomTable (of particular type and number)
  
• create a customer in customerTable
  
• create a reservation in reservationTable, and set it's customerIDNumber to that of the new customer, and it's roomIDNumber to that of the retrieved room.
  
• Finally, set the retrieved room's reservationIDNumber to the new reservation's ID number.

So I have a two-part question:
1) should I use a transaction to do this (and how)?

2) since the *IDNumber's are auto-generated by access, how do I get (and hold) their ID numbers without another requery after their creation?


I have been leaning toward the AS keyword (it can be used a variable in a transaction, right?) and am trying out the "transaction" block. But both are giving me errors in Excel. Can it be done in excel?

If it can't, I'm really not sure how to proceed, except to (redundantly) retrieve the room through 1 query, store the ID in a variable. Then create a customer. Then query for that customer so I can access/store it's ID. Then create a reservation and then query it so I can access/store it's ID. Finally, using 2 final sql statements, make the appropriate updates in reservationTable and roomTable.

But that would be 7 DB queryies/updates- and that seems riduculously complicated, inefficient, and in fact a drain on resources.

So what would be the best way to do that?

Specific question:

I have been a problem with using an INSERT SQL statement in a VBA sub. Excel is being used as a frontend to an access db (long story short- the customer wanted it to be in excel, even though it really should have been done in Access and used OLE to create the spreadsheets.)

Here's the SQL code that is being created:

INSERT INTO yearTable(year, startDate, endDate, currentYear) Values('2076', 'June 29', 'July 18', true)

But I keep getting a "runtime error- syntax error in INSERT INTO statement"

Yet the SQL INSERT statement follows both the general signature and also examples that I have in my SQL books.

I tried adding a ';' at the end (like an access query SQL). Still no dice.

The code generating this is:

'after code using inputBoxes to get the referred-to-below variables.

dim conn As ADODB.Connection
dbName = ThisWorkbook.Path & "globexlDB.mdb"
Set conn = New ADODB.Connection
conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbName & ";")

createNewYearSQL = "INSERT INTO yearTable(year, startDate, endDate, currentYear)" & _
                            " VALUES('" & newYearYear & "', '" & newYearStart & "', '" & newYearEnd & _
                            "', true)"
                    MsgBox (createNewYearSQL)
                    
                    conn.Execute (createNewYearSQL)

What am I doing wrong?
This makes no sense.

Thanks for your help,

Ian Ohlander
ianohlander@yahoo.com

 

[dk]

Hi Ian,

The answer to your specific question is fairly straightforward. Just put square brackets around the Year part of your statement e.g.

createNewYearSQL = "INSERT INTO yearTable([year], startDate, endDate, currentYear)" & _
                            " VALUES('" & newYearYear & "', '" & newYearStart & "', '" & newYearEnd & _
                            "', true)"

This will force it to realise that Year is a field name, and not a function (or whatever else it thinks it is).

<hr>

As for your other questions. To get the value of the auto generated number just do something like this....assumes you already have an open connection:-

Set adoRS = New ADODB.Recordset
adoRS.Open "SELECT * FROM RoomTable WHERE BLAH BLAH", conn, adOpenKeyset
adoRS.AddNew
MsgBox adoRS.Fields("IDNumber")

Using a transaction is a good idea. You can make sure that either everything gets updated, or nothing at all (if something ****s up).

To use, follow this template:-

    On Error GoTo errHandler

    conn.BeginTrans


    'All updates to the database won't take place until you call
    'the CommitTrans method of the Connection object

    'Do various things


    conn.CommitTrans

    Exit Sub

errHandler:
    conn.RollbackTrans

I hope that gives you enough info to go on for now.

 

[ianohlander]

You are fantastic, DK.

I will use that template.

I am, however, a little green (in just about eveything ) with the recordSet object. I tried setting it's properties and then to see if it supported adNew. None of them did. Won't that prohibit me from using addNew or update?

Ian

 

[dk]

Really? What sort of recordset are you opening (probably best to post your code)? If your data source is an Access database then you should be able to add new records to any recordset type as long as the LockType isn't read-only.

Remember, if you're trying this:-

Set rs=conn.Execute("SELECT * FROM xxx")

you'll end up with a read-only recordset. It's probably easiest if you do it like this:-

Set rs=New ADODB.RecordSet
RS.Open "SELECT * FROM Whereever", conn, adOpenKeyset, adLockPessimistic

 

[ianohlander]

As it turns out, it was not a problem.

But I am ready to kill.

I have kept 3 copies of my application, 2 on HD and one on a floppy. This morning, before I left for work, I wanted to look at what I had done last night. It was working relatively well, seemed stable, and so I was ready to tackly the final stretch. But I noticed that a comboBox of mine was not working. Puzzled, because I had made no changes to it, I stepped through the code, and I saw it was being populated as it should. Before, even last night, when I clicked the drop down, I saw a list of customers I could select. WHen I did, it would then bring that customer's information up. But this morning, it was empty. But when I stepped through, it was populated. Yet, I had done nothing to that code.

Anyway, I thought that it might be being modified or something done to it somewhere else (which I doubted, since I could remember everything I had done last night.) I knew I would find the problem.

Well, this evening, I cannot open the application- at all. It gives me the macro- warning (and macros enabled or disabled) then says it's opening globeXL.xls. But it never does. It just sits there. If you click the screen, it says it is not responding.

Same thing with the back up on the HD and the one on the floppy.

I tried uninstalling/reinstalling office twice. Still nothing. I just "norton anti-virus-ed" my computer. Nothing.

3 weeks of work gone. I have detailed notes, flowcharts and sequence diagrams, but still. ALL MY WORK IS GONE!!!

I am furious. I tried ExcelRecovery, but it could do nothing either.

Don't know what else to do. I wanted to be done by tomorrow.

Sorry. Just venting. I am so full of rage, right now.

Ian

 

[dk]

Ian,

I offer you my condolences

I've been there (as I'm sure most of us have) and I've shouted and sworn at my PC in an office full of people!

Do you use Excel XP? I haven't got it but I understand it has better recovery than other versions.

Anyway, check your private messages.

 

[ianohlander]

Thanks.

I am using Excel 2000. One of the wierd things (aside from that fact that every single one of my copies of this applicaiton were affected- wierd) is that when it starts it asks if I want to run the macros. If I click 'no' (so that it's basically going to ignore the modular VBA code- which is where the problem would have to be, right?) Excel still freezes up. I have to 'end program'.

The only thing I can think of that might even be the problem is that it uses ADODB to connect to an access DB. When I ran ExcelRecovery, the one message I did see (in the log) was that "the OLE structure had been broken". Thing is, I'm not using any OLE objects, so it sounds like a behind-the-scenes housekeeping chore of Excel. And for some reason, something happened to interfere with what was obviously a crucial process.

But I cannot get at it. Maybe it could be fixed easily if I could. But none of 3 copies will open. I had no data to save, so that's one headache out of the way. But all the code (including help you guys gave), the interaction, the forms- everything- is basically locked up and not even accessible for a simple 'cut-and-paste' transplant to a new incarnation.

Grrr.

Anyway, thank you guys for your help. The rewrite will be easier for it. I just wish I knew what I needed to avoid so it doesn't happen again

Ian