I need to create A materialized query in Access VBA (1 Viewer)

[AccessVBANewbie]

I need to create a new table in current databases using ADODB whose properties are defined in a lookup table.
Public Function Create table()
Dim cnnDB As ADODB.Connection
Dim myRecordSet As ADODB.Recordset

Dim tblname As String
Dim qry_Issues As String
' Get connection to current database.
Set cnnDB = CurrentProject.Connection
Set myRecordSet = New ADODB.Recordset


qry_DataCheck = "SELECT RequiredFieldName, RequiredFieldDataType " & _
"FROM lk_RequiredFields;"
Set myRecordSet = cnnDB.Execute(qry_DataCheck)
If Not myRecordSet.EOF Then
myRecordSet.MoveLast
myRecordSet.MoveFirst
Do While Not myRecordSet.EOF
Debug.Print myRecordSet.Fields("RequiredFieldName ")
Debug.Print myRecordSet.Fields("RequiredFieldDataType ")
myRecordSet.MoveNext
Loop
Else
Msgbox " lookup table is empty. How can I look for something that does not exist"
End If
‘How do I make now the new table that does not exist yet with as many columns as set in lookup.
‘The new table properties are based on above table.

cnnDB.Close
Set myRecordSet = Nothing
Set cnnDB = Nothing
Exit Function
Again thank you very much for your time.

 

[jdraw]

What happens when you run this code? Error?

 

[AccessVBANewbie]

it runis and displays row by row all fied names and datatype. thats all it does.

 

[Galaxiom]

Build an SQL CREATE TABLE command string using the information in the recordset. Then execute it.

 

[AccessVBANewbie]

I have no I deal how to do it as the result of the record set above can be very large or small. For example 30 columns or 5 columns each time the lookup table is updated.
Can you please show me an example I know that is too much to ask.
Thank you for your advice.

 

[AccessVBANewbie]

Sorry Galaxiom I meant to say I know how to do the CREATE table in code. It’s the putting of the stuff together dynamically before the CREATE and the columns can be very big or small as stated above.

I really hope that is very clear.

Thank you very much in advance.

 

[Galaxiom]

All that is required is a loop through the recordset with the field information. There are endless examples of this kind of coding available.

Concatenate this information into a string so that it forms a valid sql command and execute it.

Size is of no importance unless you exceed the maximum number of characters in a command. It is quite substantial. Even then the basic table can be created then modified with ALTER TABLE command.

 

[AccessVBANewbie]

Hi
Below is a loop that displays the required info, how do I Concatenate this information into a string so that i build the sql Create command. as you can see when I get out of the loop I only get one result the last one but I need to stay in the loop to get all one by one. how do I store the results and build the sql Create dynamically.

[COLOR=black][FONT=Verdana][COLOR=black][FONT=Calibri][FONT=Calibri][SIZE=3]Public Function Create table() [/SIZE][/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Calibri][FONT=Calibri][SIZE=3]Dim cnnDB As ADODB.Connection[/SIZE][/FONT][/FONT][/COLOR][FONT=Calibri]
[SIZE=3][FONT=Calibri][COLOR=black]Dim myRecordSet As ADODB.Recordset[/COLOR][/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri][COLOR=black]Dim tblname As String[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Calibri][COLOR=black]Dim qry_Issues As String[/COLOR][/FONT][/SIZE][/FONT]
 
 
[COLOR=black][FONT=Calibri][FONT=Calibri][SIZE=3]' Get connection to current database.[/SIZE][/FONT][/FONT][/COLOR][FONT=Calibri]
[SIZE=3][FONT=Calibri][COLOR=black]Set cnnDB = CurrentProject.Connection[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Calibri][COLOR=black]Set myRecordSet = New ADODB.Recordset[/COLOR][/FONT][/SIZE]
[/FONT]
[COLOR=black][FONT=Calibri][FONT=Calibri][SIZE=3]qry_DataCheck = "SELECT RequiredFieldName, RequiredFieldDataType " & _[/SIZE][/FONT][/FONT][/COLOR][FONT=Calibri]
[SIZE=3][FONT=Calibri][COLOR=black]"FROM lk_RequiredFields;"[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Calibri][COLOR=black]Set myRecordSet = cnnDB.Execute(qry_DataCheck)[/COLOR][/FONT][/SIZE][/FONT]
 
 
[COLOR=black][FONT=Calibri][FONT=Calibri][SIZE=3]If Not myRecordSet.EOF Then[/SIZE][/FONT][/FONT][/COLOR][FONT=Calibri]
[SIZE=3][FONT=Calibri][COLOR=black]    myRecordSet.MoveLast[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Calibri][COLOR=black]    myRecordSet.MoveFirst[/COLOR][/FONT][/SIZE][/FONT]
 
 
[COLOR=black][FONT=Calibri][FONT=Calibri][SIZE=3]Do While Not myRecordSet.EOF[/SIZE][/FONT][/FONT][/COLOR][FONT=Calibri]
[SIZE=3][FONT=Calibri][COLOR=black]     Debug.Print myRecordSet.Fields("RequiredFieldName ")[/COLOR][/FONT][/SIZE]
[SIZE=3][FONT=Calibri][COLOR=black]     Debug.Print myRecordSet.Fields("RequiredFieldDataType ")[/COLOR][/FONT][/SIZE][/FONT]
 
[COLOR=black][FONT=Calibri]  myRecordSet.MoveNext[/FONT][/COLOR]
 
[FONT=Calibri][COLOR=black]Loop[/COLOR][/FONT]
[FONT=Calibri][COLOR=black]Else[/COLOR][/FONT]
[COLOR=black][FONT=Calibri]     Msgbox " lookup table is empty. How can I look for something that does not exist" [/FONT][/COLOR]
[COLOR=black][FONT=Calibri]End If[/FONT][/COLOR]
 
[/FONT][/COLOR]

Please excuse my ignorance
Once again thank you very much for all your advice.
Please any chance you can show me.
Thanks you very much