Excel -> Access Table

[Patonar]

Hi,

I would like to be able to using VBA from an Excel macro - go through
each line in my spreadsheet and add to a database.

I know that this can be done via the import external data tool in
access but the final solution i am working towards will allow the user
to select columns from randomly formatted datasets in excel.

I already have code that loops through every row in my excel file to
format it correctly so it isn't the loop i am worried about. It is the
actual insert code from vba i need to use.

Preferably without using a DSN connection if possible and instead
naming the direct route to the database - since this is going to be
stored over the network.

Regards,

Andy

 

[Norman Yuan]

You can use either DAO or ADO with DSN-less connection in your Excel's VBA
code to send data from the sheets to Jet database (*.mdb). psuedo-code like
this:

Dim cn As ADODB.Connection
Set cn=New ADODB.Connection
cn.Open yourConnectionStringToTheDatabase

''In your loop for each row of the sheet
For each sheetrow of the sheet
cn.Execute "INSERT INTO theTable (Col1, Col2....) VALUES (cell1Value,
cell2Value...)"
Or
cn.Execute "UPDATE theTable SET ....WHERE..."
Next row

cn.Close

Or you can open a RecordSet based a table in the database. Than populate the
RecordSet with data from Excel sheet and do the update the RecordSet back to
the database at the end.

With this kind of approach, you have the full control and the flexibility to
match user selected columns to certain table in the database and do the data
transfer.