VBA Excel to SQL Server

[paulanthony]

Hi everyone - im trying to develop a solution whereby an existing excel spreadsheet gets submitted to an online SQL server database. The excel sheet is flat one dimensional and without formula. I have currently got all the information from the sheet into an array...my question is how to get the information into sql server from VBA. Can I use something like the following.. Dim DB As DAO.Database Set DB = DBEngine.OpenDatabase("server=localhost;database=blah") or do i need to implement webservices...next question is can a webservice handle an array as a parameter or do i need to rethink my logic at the spreadsheet level. I have pulled code snippets from google, and there seems to have been so many different providers database engines / references needed that im completely confused. What should i be using with Excel 2002 - what is the most stable db engine.

[Mandar Patankar]

'you may need to add refrences for this to work look ADO library 'DataBase Connection parameters Public stConn As String 'Connection String Public Cnt As New ADODB.Connection 'Connection Object public StConn as String stConn = "Provider=sqloledb;" & _ "Data Source=ServerName;" & _ "Initial Catalog=Database;" & _ "User Id=sa;" & _ "Password=yourpassword;" Cnt.OPEN strconn Qry_str = "your Query Insert,Update,Delete;" Cnt.Execute Qry_str Mandar Patankar Microsoft Certified professional

[paulanthony]

Thanks for the reply Mandar Dim strConnection, Password, User, InitialC, Source Source = "PAULANT" 'Your server name User = "sa" 'The SQL user you want to use (probably 'sa') Password = "blah" 'The password for the previously defined User InitialC = "blah" 'The database you wish to maintain strConnection = "Provider=SQLOLEDB;Persist Security Info=False;Password=" & Password & ";User ID=" & User & ";Initial Catalog=" & InitialC & ";Data Source=" & Source Set objCommand = New ADODB.Command objCommand.ActiveConnection = strConnection ''' Load the SQL string into the Command object. objCommand.CommandText = "INSERT INTO tbl_register (fld_name) Values ('test')" ''' Execute the SQL statement. objCommand.Execute - This works - but using an IP Address doesn't work....I want to perform this command with a remote SQL Server

[ulchris]

Add another parameter in your connect string: "SERVER=999.999.999.999;" Like so - Dim strConnection, Password, User, InitialC, Source, Server Server = "999.999.999.999" 'Set your own IP here Source = "PAULANT" 'Your server name User = "sa" 'The SQL user you want to use (probably 'sa') Password = "blah" 'The password for the previously defined User InitialC = "blah" 'The database you wish to maintain strConnection = "Provider=SQLOLEDB;Server=" & Server & ";Persist Security Info=False;Password=" & Password & ";User ID=" & User & ";Initial Catalog=" & InitialC & ";Data Source=" & Source Goodluck! Cprompt