1

I have an Excel worksheet where I can insert Data into a MS SQL Database. When closing this worksheet, a mail with the sql-script attached should be generated and send automatically. The script includes all the create table, sequences and insert statements.

I can generate the sql script manually in the MS SQL Studio, but maybe there´s a method (here: generate_sql_script) to generate the script automatically.

Here´s my code for generating the mail:

Dim objOutlook, objMail, sql_Script As Object
Set objOutlook = CreateObject("Outlook.Application")

sql_Script = generate_sql_script

With objMail
    .To = "myEmail@test.com"
    .Subject = "new sql script attached"
    .Body = "Dear Sir or madam..."
    .Attachments.Add sql_Script
    .Send
End With

Thanks for any help in advance.

jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
Dave
  • 37
  • 3
  • Are you talking about MS SQL? So tag your question correctly and remove the MySql tag – nacho Apr 03 '18 at 13:44
  • How are you expecting to generate this **"sqp-script"**? – Zac Apr 03 '18 at 13:57
  • @TrevorD nothing really. I tried to "record the macro" in Excel, but it didn´t work. I don´t know if my idea of writing a method, that generates the script for me is even possible. – Dave Apr 03 '18 at 14:03
  • @nacho yes I´m talking about MS SQL.. sorry for the mistake. – Dave Apr 03 '18 at 14:05
  • @Zac THAT´S my question. – Dave Apr 03 '18 at 14:05
  • What generated the SQL script in the first place? Does it create a text file or .sql file you can read? – Trevor Apr 03 '18 at 14:20
  • @TrevorD the sql script includes all the create table, create sequence and insert statements to create the database completely new. When create the script manually I proceed this way: *in MS SQL Server Management Studio* -> select the database -> rights click on the database -> select *Tasks* -> select *generating script* -> continue the procedure and make sure you generate the scheme **and** the data. Result will be a .sql script – Dave Apr 03 '18 at 14:33
  • @TrevorD I have this database twice. one is only for tests. And when the tests are sucessfully I thinks it´s easier to create the database completelly new instead of writing all the *update statements*. It is a very small database.. – Dave Apr 03 '18 at 14:41
  • Possible duplicate of https://stackoverflow.com/questions/1695738/programmatically-generate-script-for-all-objects-in-a-database Does that question help you? – Trevor Apr 03 '18 at 14:42
  • This is `SQL Server` isn't it? Getting confused with the `MS SQL` and `MS SQL Studio` references. Maybe add the tag for it? – Darren Bartrup-Cook Apr 03 '18 at 15:31
  • I'm extremely doubtful this type of SQL-integration functionality exists in Excel. – Ian Peters May 04 '18 at 16:57

0 Answers0