Migrating Excel Table to SQL Server 2005

[OrlaLynch]

Hi,

I am having a lot difficulty trying to get an Excel 2007 table import into a SQL Server 2005 table. I have looked through the internet and cannot find any help other than download software. The main problem I have come across is that I want the user to be in the Excel and click a macro button to import the data.

The code I have been trying is:


Dim con As Object

Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=SQLOLEDB.1;Data Source=ORLA-PC\SQLEXPRESS;" & _
"Initial Catalog=Edpac;Trusted_Connection=Yes"
con.Execute _
"SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'," & _
"'Text;Database=C:\Users\Orla\AppData\Local\Temp\;','SELECT * FROM [Book1.xls]')"

I did find a web page that told me about permissions so I did everything they told me so every group and user name was allowed full control in the Temp folders.

Any help would be greatly appreciated!
Thanks,
Orla

[Jan Karel Pieterse]

Can you import external data from the SQL server database using Data, get external data, new database query? If so, record a macro while setting one up.

[OrlaLynch]

Hi,

Thanks for the reply but I don't have that function in my SQL, I think it's because it's SQL Server Express.

Thanks again,
Orla

[Jan Karel Pieterse]

The function is in Excel, not in SQL server!

Just import some data from SQL server into Excel .
Doing this whilst recording a macro will give you the connection string. This string can then be used to use ADO to update your SQL table from Excel.

[OrlaLynch]

I have tried your theory which helped with my database connection, it is now playing with no errors. However I am getting an error on my Select statement. The error I am getting is : "OLE DB provider "Microsoft.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".

This is what I am now using:

Dim con As Object

Set con = CreateObject("ADODB.Connection")
con.Open _
("Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=ORLA-PC\SQLEXPRESS;Initial Catalog=ExcelTest")
con.Execute _
"SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Users\Orla\AppData\L ocal\Temp\;','SELECT * FROM [Book1.xls]')"


Thanks

[Jan Karel Pieterse]

I don't think you can update a SQL server table this way, I'd use a different SQL statement all together, which must be either in the shape:
INSERT INTO....
for new records, or
"UPDATE ..."
for existing records.

But I'm no SQL expert by a long stretch.