i want to connect excel with sql server 2000 as database with macr
 

i have a table Transcription_temp in sql server2000 i have to import data
from excel daily. My excel file name is today.xls
my excel headings are
Userid Date TID PID Pname Dname Lines Wtype Template Clients


my table header names are

UserID TranscrioptionDate TranscriptionID PatientName
DoctorName LineTranscribed worktype TemplateName
ClientName

i want a macro to connect sql with excel and export the data of excel file
today.xls
to sql server 2000

khurram saddique wrote:
i have a table Transcription_temp in sql server2000 i have to import
data
from excel daily.
i want a macro to connect sql with excel and export the data of
excel file
today.xls
to sql server 2000

The following SQL code may be used for an import or an export:

Sub just_four_lines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=SQLOLEDB.1;Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;User Id=sa;password=;"
con.Execute _
"INSERT INTO Transcription_temp (UserID," & _
" TranscrioptionDate, TranscriptionID, PatientID," & _
" PatientName, DoctorName, LineTranscribed, worktype," & _
" TemplateName, ClientName)" & _
" SELECT UserID, TranscrioptionDate, TranscriptionID," & _
" PatientID, PatientName, DoctorName, LineTranscribed," & _
" worktype, TemplateName, ClientName" & _
" FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
"'Excel 8.0;HDR=YES;Database=C:\Today.xls'," & _
"'SELECT Userid AS UserID, [Date] AS TranscrioptionDate," & _
" TID AS TranscriptionID, PID AS PatientID," & _
" Pname AS PatientName, Dname AS DoctorName," & _
" Lines AS LineTranscribed, Wtype AS worktype," & _
" Template AS TemplateName, Clients AS ClientName" & _
" FROM [MySheet$]');"
End Sub

Jamie.

--

sql server 2000
i am expriencing a problem when i run the macro in excel 2003
an error message displays
runtime error 2147467259
DBNETLIB[ConnectionOpen(Connect()].Sql server does not exist or access denied

i have given the path of today.xls file
((((((Excel 8.0;HDR=YES;Database=C:\\mt\Today.xls'," & _)))))

in sql server 2000 in console route
in microsoft sql servers
in pc53(windowsNt)
in databases
in CresMt
in tables Transcription_temp
and in excel there are two spreadsheets mt3 and qc3 where the data has to be
exported to sql server 2000
plz help cause manually a lot of time is waisted
regards






"Jamie Collins" wrote:


khurram saddique wrote:
i have a table Transcription_temp in sql server2000 i have to import
data
from excel daily.
i want a macro to connect sql with excel and export the data of
excel file
today.xls
to sql server 2000

The following SQL code may be used for an import or an export:

Sub just_four_lines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=SQLOLEDB.1;Data Source=MYSERVER;" & _
"Initial Catalog=MYDATABASE;User Id=sa;password=sa;"
con.Execute _
"INSERT INTO Transcription_temp (UserID," & _
" TranscrioptionDate, TranscriptionID, PatientID," & _
" PatientName, DoctorName, LineTranscribed, worktype," & _
" TemplateName, ClientName)" & _
" SELECT UserID, TranscrioptionDate, TranscriptionID," & _
" PatientID, PatientName, DoctorName, LineTranscribed," & _
" worktype, TemplateName, ClientName" & _
" FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
"'Excel 8.0;HDR=YES;Database=C:\Today.xls'," & _
"'SELECT Userid AS UserID, [Date] AS TranscrioptionDate," & _
" TID AS TranscriptionID, PID AS PatientID," & _
" Pname AS PatientName, Dname AS DoctorName," & _
" Lines AS LineTranscribed, Wtype AS worktype," & _
" Template AS TemplateName, Clients AS ClientName" & _
" FROM [MySheet$]');"
End Sub

Jamie.

--