How do I get the CREATE TABLE part from a table creation query?

[Javier Hernández]

Hi everyone. I'm not a programmer by career, so I may be unfamiliar with some terms. I have done VBA programming in Excel but now I want to foray into the Access database in my work. Gotta admit it has been harder than I thought. The mix of the DAO, ADO and DoCmd libraries have been giving me some trouble.

Anyway, right now I want to execute within a form an Access table creation action query with one entry parameter (let's call it GetWorkload from now on). I made it with the usual querybuilder view. To ilustrate my problem (although these you probably know well), I have tried the following:

• Creating an ADO recordset referencing the GetWorkLoad query. Problem: adcmdTable option will simply put SELECT * FROM... and will turn it into a selection query.
• Try to get the GetWorkLoad string with the .SQL property from QueryDef. Problem: for some odd reason Access doesn't get the CREATE TABLE part of the query creation table and only starts from the SELECT part. Which property or method can return that part? I cannot see it even in the SQL view.
• Using the DoCmd.OpenQuery method. Problem: the parameter value part from the QueryDef.Parameters collection is read only (why?), so I can't set the parameter using code.

A former employee bypassed this issue by referencing the field to the value of a hidden textbox in the form. I find this solution rather unelegant since it ties the query to the form and denies me from using it anywhere else. Of course, I can put all the CREATE TABLE SQL part right into the code, but then I alredy envision wasting HOURS debugging SQL sentences on the VBA editor, checking all the parenthesis, the multiple JOINs, etc.

I feel there has to be a way to switch off those user friendly barriers and display the DDL sentences used on the Access queries, or a way to work directly with the Access queries instead of recordsets that merely reference them.

Any help is appreciated.

[June7]

Why do you need to use CREATE TABLE? There might be better ways to accomplish whatever you are doing than with CREATE TABLE.

Post the query statement for analysis and VBA code.

I am not familiar with adCmdTable. Never used "Options" with a recordset.

I copy/paste sql statements from Access query objects into VBA. I don't consider time debugging VBA SQL any more a waste than what seems you've already gone through trying to modify a query definition.

[Javier Hernández]

My bad. I thought there had to be a CREATE TABLE sentence in a table creation query. I learned until now that SELECT INTO is the part that actually creates the new table (yes, I am still new to this stuff).

This is the sentence on my Access query object:

PARAMETERS IdMuestreador Long;
SELECT Muestras.FOLIO, True AS Expr1, naturaleza.Naturaleza INTO Etiquetas_Testigos
FROM ((Muestras INNER JOIN datos_cadenas_externas ON Muestras.FOLIO = datos_cadenas_externas.folio) LEFT JOIN Muestreadores ON datos_cadenas_externas.Idmuestreadores = Muestreadores.Idmuestreadores) INNER JOIN naturaleza ON Muestras.Idnaturaleza = naturaleza.Idnaturaleza
WHERE (((datos_cadenas_externas.[FECHA DE RECEPCIÓN EN EL LABORATORIO]) Is Null) AND ((Muestras.Folio_cancelado)=False) AND ((Muestras.Idtipomuestreo)=1) AND ((datos_cadenas_externas.Idmuestreadores)=[IdMuestreador]));


This is the part of the code that executes the query:

DoCmd.SetWarnings (False)
strconsulta = CurrentDb.QueryDefs("Crear_Etiquetas_Testigos").SQ L


Comando.ActiveConnection = CurrentProject.Connection
Comando.CommandText = strconsulta
Comando.CommandType = adCmdText
Comando.Parameters("IdMuestreador").Value = CuadroMuestreadores.Value
Comando.Execute
DoCmd.SetWarnings (True)

'
rsttipos.Open "Etiquetas_Testigos", acNormal, acEdit, adCmdTable
Do Until rsttipos.EOF
If rsttipos.Fields("Idnaturaleza") = 5 Or rsttipos.Fields("Idnaturaleza") = 17 Then
rsttipos.Fields("Expr1") = True
Else
rsttipos.Fields("Expr1") = False
End If
rsttipos.MoveNext
Loop
rsttipos.Close


DoCmd.OpenReport "Testigos_a_Imprimir", acViewPreview
End Sub

Right now I'm getting the "Table already exists" error ("Etiquetas_Testigos" is a table which is expected to be renewed daily) Is there a way to make ADO overwrite the former table or do I need to manually delete it before executing the query?

[Javier Hernández]

Well, for now I have added a DelectObject line to the code so the former Etiquetas_Testigos table gets deleted before the new one is created. It can be expected for that table to always exist so I don't need to confirm if it exists before deleting.

I'm still interested if people know of ways to change parameter values within the DAO library since it seems to be better suited than ADO to manipulate Access objects (I have been given an ADO reference book, so I'm more used to that, but a DAO tutorial would be welcome).

[June7]

If you want to overwrite all data in table, just delete all the records before INSERT sql. More efficient than repeatedly creating and deleting table.

CurrentDb.Execute "DELETE FROM tablename"
CurrentDb.Execute "INSERT INTO ... SELECT ..."

Is this a split database? The project connection code is not necessary if table links are established. Also, SetWarnings not needed with Execute method.

[Javier Hernández]

I'm definitely doing that. There are noticeable lags while performing some tasks and anything that helps reducing them is welcome.

To answer the other question, yes, the database is split among several mbd archives although all the tables are linked. To be honest, I still don't grasp fully the concept of a database connection, and I added the code to prevent "getting a different connection created for each new recordset" as I read before, although I'm not sure if it applies here.