Editing a SQL database using VBA?

I am in a hurry to find out if I can edit a SQL database using VBA. How doable is this? To what extent? What code will I need for this?

To improve the usefulness of the responses, here's what I'm working with:

• SQL skill level = I am trained in SQL. Done several courses (some twice) through different providers. I humbly rate myself as basic/amateur skill. (I was forced to work with vanilla SQL and with TSQL using SSMS compatibility level 90 for many of my early SQL years. Consequently I am aware of but uncomfortable with using more recent features such as OVER)
• VBA skill level = I consider myself above-average experience with VBA (Some forum members may disagree! I freely admit that many of the threads I start would appear highly unusual if you were unaware that they were a mere part in a much larger - and more complex - whole! )
• I have used VBA for several years to read from different types of databases
• Environment = The SQL database in question is on MS SQL Server (2008 r2). I have full admin access to the database and can read data from it using VBA.

So can I UPDATE and INSERT into tables?

Could I DROP a table?

Could I use VBA to remotely add a new table to the SQL DB?


What would be most useful to me now would be a VBA function that would allow me to replace a certain table with a 2d array. But IDK how. And there's the old problem of data types.

Originally Posted by Kyle123

Yes, you can do all of it. Executing SQL using ADO is no different to executing SQL via SSMS.

Good Morning Kyle (or is it a late nighter? ) I'm not sure how to go about doing edits to a SQL server DB from VBA.

Would it help if I sent you the VBA module I use for SQL work?

Originally Posted by Kyle123

you just use an update or insert statement instead of select. You can even use the recordset object, though it isn't really necessary - you can simply use the execute method of the connection object, just pass it the SQL you want to execute.

That helps, thanks.

Originally Posted by Kyle123

I think you're over thinking this one.

I probably am. It's just that I have had major issues with table data types in the past. So I am floundering on how I can upload a 2d array (in VBA) to form a table in a DB on a SQL server. The array contains all the raw data that will be in the table so rather than trying to identify what are the new records and what are current records and need/doesn't need updating, I would prefer to drop the table and replace it completely. But then I know I will run into a wall with the data types.

I guess I should learn more SQL but finding the time and money would be difficult now (I haven't even finished working through the last VBA book I bought)

That doesn't really sound like you are using the database properly as that breaks normalisation.

Still though, why drop and create the table rather than delete all the records and then insert?

Finally got it working thanks to Kyle. Reps.

I had to go with a cnn.Exec and a loop to INSERT each row of each table (Every other method is too modern for VBA-SQL limitations!)

It's slow but it works so marking thread solved.

Originally Posted by Kyle123

I think I’d go for something like creating a cvs from the data, then saving it somewhere that is accessible from the database. If then create a sproc to use bulk insert or openrowset to read the cab and insert the data.

My SQL knowledge is not good enough to have a SProc import the data.

Also small problem that the source is not accessible from the database and will need regular uploading.

Originally Posted by Kyle123

If you need it running regularly, I’d create an agent job to call the sproc at whatever interval you need. If not, you could simply call the sproc from VBA.

I love writing SQL agents but I cant do the sproc (see previous)

BTW I could be wrong but I don't think it's possible to do a BULK INSERT through ADO OLEDB (Same reason why I had to drop the TPV method I was looking into)

Originally Posted by Kyle123

The other alternative is to simply loop through the array/csv and run an insert for each row.

Which is what I ended up doing.