SQL With String TableName

[Psycholicon]

Hi, I'm learning SQL (and Access for that matter) as I go, so I apologize for having terrible syntax, but if you could help me anyway, it'd be a lifesaver.

In my DB there are notices that are issued that make revisions, additions, or deletions to drawings. However, some of these notices do not get finalized, and so everything has to be tentative right up until the end, when it is released. My way of dealing with this is to duplicate the master Drawing Log table and retitle it with the number of the notice (ex. 07-23 Log). Then the edits are done in this table and when the notice is released they are transferred to the master table and the duplicate is deleted. The tables are made fine and work, but when I try to use SQL statements to update and insert into the duplicate, the TableName string doesn't work for some reason. If I put in the actual name, it works, but in operation that has to be variable. I know it's something easy, but I can't figure it out, and I'm tearing my hair out!.

Sorry for the long explanation. Here's some of the sample code:

To add:

Number = Me.Parent.DCN_Number.Value
TableName = Number & " Log"
DoCmd.RunSQL "INSERT INTO " & TableName & " ([Drawing Number],[Description],[Last Date]) VALUES ('" & AddDrawingNumber & "','" & DrawingDescription & "',#" & Me.Parent.[DCN Date] & "#)"

To delete:

Number = Me.Parent.DCN_Number.Value
TableName = Number & " Link"
DoCmd.RunSQL "DELETE FROM " & TableName & " WHERE [Drawing Number] = '" & DeleteDrawingNumber & "'"

To revise (CurrentRevision comes from code that moves it one letter up)

Number = Parent.DCN_Number.Value
TableName = "" & Number & " Log"

DoCmd.RunSQL "UPDATE " & TableName & " SET [Current Revision] ='" & CurrentRevision & "' WHERE [Drawing Number] =" & "[Forms]![DCNs]![DCN Details Subform]![Drawing Number]"

DoCmd.RunSQL "UPDATE " & TableName & " SET [Last Date] ='" & Me.Parent.[DCN Date] & "' WHERE [Drawing Number] =" & "[Forms]![DCNs]![DCN Details Subform]![Drawing Number]"


Please don't be angry with how horrible it looks. Any pointers you could give on better efficiency would also be greatly appreciated, but I am really amateur when it comes to programming.

 

[KeithG]

What error do you get when you use the variables?

 

[Psycholicon]

Oh, I'm sorry, I completely forgot.

Now it is just giving me syntax errors in all three. When I tried having a notice just titled TEST, even though this made a log called TEST Log, the error was "cannot locate table or query 'TEST' "

Now it's just looking like all of them are written wrong, but I am assuming it still ahs something to do with the table name

 

[KeithG]

Is there a space in the table name? If so try adding brackets around the table name. Like below

DoCmd.RunSQL "INSERT INTO [" & TableName & "] ([Drawing Number],[Description],[Last Date]) VALUES ('" & AddDrawingNumber & "','" & DrawingDescription & "',#" & Me.Parent.[DCN Date] & "#)"

 

[Psycholicon]

thanks, Keith. That fixed up the problem. I had tried bracketing, but forgot to put them inside the quotes. SQL runs circles around me. Now the problem is that the SQL runs and nothing happens on the table, but as long as it's written right and runs, the data's going somewhere, and I can find it soon enough. Thanks!

 

[Psycholicon]

Oh, wait a minute. Now it's coming back with 'cannot locate input table or query 'stDocName'

 

[KeithG]

Can you post an example of you db?

 

[tehNellie]

One thing that might help in the first instance is to place your SQL into a string Variable which will make it easier to double check the syntax for errors ie:

Dim strSQL as string

strSQL = "INSERT INTO [" & TableName & "] ([Drawing Number],[Description],[Last Date]) VALUES ('" & AddDrawingNumber & "','" & DrawingDescription & "',#" & Me.Parent.[DCN Date] & "#)"

debug.print strSQL
docmd.runsql strSQL

If you step through your code using F8, you'll get the output of strsql in your immediate window before the docmd statement tries to execute it and you'll hopefull be able to see any problems with your SQL.

 

[Psycholicon]

how do I attach the DB?

 

[Moniker]

Zip the DB, and click on the paperclip icon in the top of the toolbar for this little message board.

Browse to the zipped copy of the DB, and click Upload.

 

[Psycholicon]

ehh, it's not letting me. It's probably my terrible computer here at the office. In any case, I've figured out where my coding issues were. Learning as you go means a lot of hair-tearing. But it is slow and steady. Stuff is getting finished. No small amount of that is due to all the help I've gotten on forums, thanks for helping me out, seeya on the boards when the next roadblock crops up!