We aren't suggesting you use different resource, just use the one you have more effectively. Access 2GB limit is not based on number of records, it is amount of data (normalized or not) and GUI objects (forms, reports) and code.
If you really must implement routine creation of fields, yes, it can be done. How many fields do you expect to add over life of db? There is a limit of 255 per table.
An SQL action statement can create field. Research ALTER TABLE https://www.w3schools.com/SQl/sql_alter.asp
But some table/field properties can only be programmatically edited with TableDefs so research that topic as well.
But I'll throw some code at you and you choose your path. Here is an example utilizing both:
Code:
Set cn = New ADODB.Connection
'connect to the backend database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
'create the test table
cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
'set table link
Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
tdf.SourceTableName = Me.tbxTestNum
tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
CurrentDb.TableDefs.Append tdf
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
While Not rs.EOF
If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
'create field in new table
cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
End If
If rs!DataType = "Text" Then
'change the AllowZeroLength default Yes to No
db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
ElseIf rs!DataType = "Number" Then
'make sure number field does not have 0 as DefaultValue
db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).DefaultValue = ""
End If
rs.MoveNext
Wend