I tried adding a comment there, but got an error. Here is the comment:
Mike:
I'm trying to do the same thing, except from Access to a SQL Server 2012 Express database. I have the connection string for the Access db correct, but I can't seem to get the SQL Server connection string sorted out.
Where your code is:
cmd.CommandText = "SELECT * INTO [MS Access;Database=" & Access & "].[New Table] FROM [Sheet1$]"
Mine is
cmd.CommandText = "INSERT INTO [SQL Server Native Client 11.0; Server=.\\SQLExpress; Database= " + SQL + "].[tblLenses] SELECT * FROM [tblLenses]";
"SQL" in this case resolves to the App_Data folder and the right file name.
"Access" resolves to the App_Data folder and the right file name.
The error is the old "No installable ISAM...", so I assume it means that I'm just not calling the SQL Express instance by the right name.
I wonder if you can help me with this issue.
Thx for the great article and I love how simple (elegant) your code is.
cestes001
Member
12 Points
41 Posts
How to import data to an .MDB file
Jan 29, 2013 07:21 PM|LINK
My app created the file code-first and the table is in place. In an excel file, there are 8407 rows that I need in a table in it.
I'm working in Visual Studio Express for Web 2012
Mikesdotnett...
All-Star
155597 Points
19981 Posts
Moderator
MVP
Re: How to import data to an .MDB file
Jan 29, 2013 08:29 PM|LINK
See this: http://www.mikesdotnetting.com/Article/79/Import-Data-From-Excel-to-Access-with-ASP.NET
Web Pages CMS | My Site | Twitter
cestes001
Member
12 Points
41 Posts
Re: How to import data to an .MDB file
Jan 30, 2013 08:41 PM|LINK
Thx, Mike.
I tried adding a comment there, but got an error. Here is the comment:
Mike:
I'm trying to do the same thing, except from Access to a SQL Server 2012 Express database. I have the connection string for the Access db correct, but I can't seem to get the SQL Server connection string sorted out.
Where your code is:
cmd.CommandText = "SELECT * INTO [MS Access;Database=" & Access & "].[New Table] FROM [Sheet1$]"
Mine is
cmd.CommandText = "INSERT INTO [SQL Server Native Client 11.0; Server=.\\SQLExpress; Database= " + SQL + "].[tblLenses] SELECT * FROM [tblLenses]";
"SQL" in this case resolves to the App_Data folder and the right file name.
"Access" resolves to the App_Data folder and the right file name.
The error is the old "No installable ISAM...", so I assume it means that I'm just not calling the SQL Express instance by the right name.
I wonder if you can help me with this issue.
Thx for the great article and I love how simple (elegant) your code is.
Mikesdotnett...
All-Star
155597 Points
19981 Posts
Moderator
MVP
Re: How to import data to an .MDB file
Jan 30, 2013 09:10 PM|LINK
That's not the same thing at all. You can use OpenDataSource to import data from Access to SQL Server:
http://msdn.microsoft.com/en-us/library/ms179856.aspx
http://bifuture.blogspot.co.uk/2012/03/importing-data-from-access-into-sql.html
Web Pages CMS | My Site | Twitter
cestes001
Member
12 Points
41 Posts
Re: How to import data to an .MDB file
Jan 30, 2013 09:41 PM|LINK
Is it not possible to INSERT INTO from an Access 2007 database into a SQL Expresss database?
hans_v
All-Star
35998 Points
6551 Posts
Re: How to import data to an .MDB file
Jan 30, 2013 10:07 PM|LINK
Yes, but the easiest way is to connect to the SQL Server database and use the OpenDataSource method, as explained by Mike