Hi All,
I am quite anxious to know how SQL can be used in VBA to transfer data programatically to an Access Database or a SQL Server Database. I find this to be quite an interesting area of ADO and Excel but I could not find resources online to accomplish the task I have in mind.
The requirement:
I need to be able to transfer data to an existing access database or a SQL server database which has predefined columns/field names. From excel I would like to read row wise and take specific cell values into variable(s) which I can then use in a SQL statement and inject into the database. During this operation if a duplicate value (i.e. primary key) is encountered then i want that cell to be highlighed in red so that the user can check why and then either ignore or correct and re-upload.
I know that there is an approach using recordset, which is what I can see the most online but I can't see examples where SQL statements are used and executed. I also need to know how people obtain the connection strings? Is there a logic or somewhere in Excel where this can be found? I know I can take it off the internet, but I'd like to know how this was obtained as i've observed that the connection strings change with the version of excel or access.
I'd also like an explanation (if possible) on what is being done when using the recordset method or SQL method
I would appreciate all the help in this regard.
Steve
Bookmarks