Excel to MS Sql server

Hi:

win2k, xp
excel 2k
MS SQL server 7.0

Here is the deal:

I have users entering data on an excel spreadsheet. I want that data to go to Ms SQl Server (local) database and into a table called logcall_table. How do I do it? More prcisely, how do i do it in detail? I can not find answers on the net and I am really lost. you can also refer to "SQL from Excel to MS SQL Server 7.0" my earlier post on this issue for more detail on how I have been trying to do it.

Thank you for your help.

AlexHi,
Open Enterprise Manager.
Tools -> Data Transformation Services -> Import Data ...
Next -> Data Source = Microsoft Excel XX
Enter the file name and continue with the Wizard !!!
Hope that it helps,
Handymac|||HI,

thanks for answering, but i want to sql the info to the server and update the db that way. The problem i faced with is the connection to the server from excel. How do i write a procedure to connect to the server and have all my sql in there?|||Do you want to update a sql table as you are entering in excel or do you want to import the excel file into a sql table ?|||if i understand you right, you want users to enter data to an excel spreadsheet, and they will "push a button in excel sheet" to update the entries into sql db when they finish data entry.

if its what you want, google for excel macro manual, VB6 and ODBC database connectivity.

my idea is to write some macro that connect to SQL as users finish data entry and push a botton say "Update to database"

hope this helps|||qha_vn,

That is exactly what I want to do.
Here is what I have done so far for access but that does not work for MS SQL Server and I can not figure out the way to code the connection. This is the way I connected to MS Access on my local machine. Now I have SQL Server and I want the user to push a button and sql the data in to the LOGCALL_TABLE in SQL Server. Additionally, should the application requier MS SQL Server Object Libraries to function?

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim AppPath, app As String

'AppPath = "c:\LOGCALL\LOGCALL.mdb"
AppPath = "c:\LOGCALL\test_Data.MDF"
'AppPath = "\\Richard\ADS_KNOW_HOW\USERSTATS\LOGCALL.mdb"
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

'conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
'conn.ConnectionString = "Provider=SQLServer;Data Source=" & AppPath & ";Persist Security Info=False"
conn.ConnectionString = "driver={SQL Server};server=Interweb;Trusted_Connection=Yes;uid =sa;pwd=Pass;database=sqlInterweb"
conn.ConnectionTimeout = 30
conn.Open

Set cmd.ActiveConnection = conn
'When the call is RESOLVED
If Range("a" & CStr(ActiveCell.Row)).Value <> "" Then
cmd.CommandText = "INSERT INTO test VALUES ('" & Range("a" & CStr(ActiveCell.Row)).Value & _
"','" & Range("b" & CStr(ActiveCell.Row)).Value & "');"
End If

cmd.Execute , , adCmdText
conn.Close|||Alex,

you are going to the right direction. have you tried ODBC? sorry i dont have much time now to write the code for you. can anybody help?