Access VBA Grid Setup

what do you intend to do with the data the user enters? is it to be saved?
one way to do this is to have a "dummy" table, ie data you delete after time the user enters ("defines") this grid, and then redefine an existing query (specifically for this grid) with the required columns. have a subform based on this query and refresh your subform on whatever parent form.

Can you describe your overall goal?
(i.e. not only the requirements - but the purpose?)

You want the user to be able to input two values - one for a number of columns - one for a number of rows, and then have that spontaneously as a grid of textbox controls to match those dimensions?
Unlike VB (where you've also included this question - so we need to make sure on this) Access can't necessarily create controls at runtime (design view is required - and an MDE would negate that possibility).
Plus it's not a good solution anyway.
Personally - I wouldn't attempt that with a grid - unless you can specify an absolute maximum number of "cells" at design time.
(For simple example suppose the maximum was 100 and the most the user could enter was 10x10).

A continuous form with a single row of controls would be easier to generate, however bear in mind that you'd lose total distinct formatting control on a "per cell" basis.  (CF can help you to an extent with that - but it's not as flexible or fast as it might be).

So - we turn to your requirements again.
What's the ultimate goal?  To store values entered in this grid?  Where?  Why?

FWIW the query method mentioned by frankytee while I was typing is possible (if you're OK with the raw query binding - many aren't -  though it's likely less critical in a situation such as this).
You'd lose pretty much *all* formatting options then of course - but if that's not an issue then that's OK.

However (depending on the version of Access) you should be able to pull the "grid" off without creating *any* temporary data objects in your application - and doing everything in memory.

I basically want to create a tool, with a set of questions along the top, and then down the side a list of possible outcomes. The user will then place a yes or No, or a tick or something ion the fields, where they feel the outcome is suitable to the question. I want the user to be able to set up this form on their own. So eneter in the number of questions and outcomes. Using VBA or something I would then like the system to create a grid with the questions and outcomes listed.

have you tried my suggestion?

So how would I base a grid on a table? Im not too sure which grid even to use?

no grid involved. use a subform set to "datasheet" view. that way it gives the appearance of a grid. since its bound to a table the data is persistent and you can then do whatever (save to a "permanent" table etc)

I have tried to use the following VBA to create a table on the fly but its not doing anything? It doesnt cause any errors, but doesnt create the table either?

Private Sub TableSetup()

On Error GoTo TableErrCatcher
Dim cat1 As ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim str1

'Reference objects for table
Set cat1 = New Catalog
cat1.ActiveConnection = CurrentProject.Connection
Set tbl1 = New Table


'Name table and append columns
With tbl1

    .Name = "tbl_Audit"
    .Columns.Append "1", adInteger
    .Columns.Append "2", adDate, 10
    .Columns.Append "3", adLongVarWChar, 4
    .Columns.Append "4", adVarWChar, 30
    .Columns.Append "5", adDouble
    .Columns.Append "6", adVarChar

End With

'Append new table to Tables collection
'and free catalog resource
cat1.Tables.Append tbl1
Set cat1 = Nothing

TableErrCatcher:
    If Err.Number = -2147217857 Then
        MsgBox "Error", vbInformation, "Cash Flow"
    End If
Debug.Print Err.Number, Err.Description
End Sub

To my mind though - the grid/table is just the intermediatory.
A means of collection a user's choices through the UI.

If you create a temporary table - then it should be just that, temporary - which you then iterate through and assign the selections into your real table structure data.

The temp table is likely the simplest method - if you don't need to respond to any events or provide formatting.
Equally the in memory solution needn't take much implementing at all.
But either way - your work is only partially done.  You need to load and persist that data in a normalized fashion.

Do you know how I would create a table through VBA? I have tried using my above code but it does not do anything? No errors either?

I'd imagine your error handling is causing the lack of reported errors.
While I wouldn't personally rely on ADOX to add the table - it'll do it.
Just make sure your field definitions are appropriate to a Jet db.
i.e. use adVarWChar and not adVarChar as you do in your final column.

i'm off now so good luck. let us know how it goes.