Exporting Primary Keys Into Access

Hi There

I have an SQL database which I need to export into Access, I know how to this however the Primary Key's does not transfer over and more importantly once exported I cannot set the primary key to auto number as their is already information in the table.

I know I can just set up the table, with a primary key set to an autonumber and import the other fields but as the primary key's are foreign keys in other tables I need the rows in the access table to have the same ID number as in the SQL database.

Hope the above makes sense to someone and any help would be greatly appreciated.you should be able to import key values into an access autonumber column with no problem

i've tested it sucessfully, and it works

what error did you get?|||Thanks for the feedback, it works fine now. I was advised wrongly elsewhere that it couldn't be done.

Very simple in the end!|||you gotta be careful with stuff you find on the internet, eh

Export to Excel

Hi there I know its possible to export data from a gridview to excel. I'm looking to export data directly from a stored procedure at the click of a button.

Somebody suggested using the following:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from table-name
When I tried executing the above lines of codes I got the following error message:
"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server."
If anyone has any idea whats wrong ... plssssssssssssssssssss ... let me know ...
Thanks in advance.Hi - I use vbscript client side for this:
<BUTTON id="exportbutton" onclick="BLOCKED SCRIPTexport_onclick()" name="exportbutton" type="button" value="Button">Export</BUTTON>
<script language="vbscript">
Sub export_onclick()
Dim sHTML, oExcel, oBook
sHTML = document.all.items("mydatagrid").outerhtml
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
oBook.HTMLProject.RefreshDocument
oExcel.Visible = true
oExcel.UserControl = true
End Sub
</script>

Hope that helps!
Cheers, Mark|||Hi Matait
Thanks for your reply.
But wont there be a problem if scripting is disabled on the browser ?
|||

You cannot use OPENROWSET because Excel is not a RDBMS(relational database management system) and yes you can run queries in Excel version XP/2003 but the queries are Access SQL not ANSI SQL because SQL Server rejects most. That said try the thread below for code sample and links for Asp.net 1.1 convert it for Asp.net 2.0. Hope this helps.

http://forums.asp.net/921410/ShowPost.aspx

|||

Hi Caddre

Thanks for your reply. That should work for me :)
Cheers