.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Problem with ODBC from MS Access 2007 to MS Sql Server 2008

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi experts, I need some help with ODBC Connection Settings from MS Access 2007 to Sql Server 2008. Here is the Scenario, I have created a Database(StagingDB) with 5 tables on SQlServer 2008 and I have  Developed an MS Access 2007 Front End using Linked tables and ODBC to Insert/ Update data into these 5 tables. This is just working fine in my Dev database. ODBC Connection Settings are pointing to my DevServer. All I wanted to do now is, I want to point the ODBC Settings to the same database on my Test Server and then to Production Server. I have Changed the Server name in the ODBC File to point it to my Test Server and If I try to Insert  some data into test server its stil going into the database on Dev server. Is this the right way to do. Please help me with this. thanks In Advance..

View Complete Post

More Related Resource Links

Problem connecting to SQL Server 2008 R2 Express from Access 2007

I have a problem with SQL Server 2008 R2 Express with MS Access 2007. I am running Windows 7 with 8 gigs memory. I installed SQL Server 2008 R2 Express. Do I now need to install SP1 because I am running with Windows 7? I can open SQL Server using the Management Screen but I cannot connect to MS Access 2007 using SQL Server Express as the database. When trying to connect to SQL Server from Access 2007, I keep getting the message that SQL Server 2005 cannot be found. It will not allow me to sign onto sql server 2008 stating that it cannot be found. Do I have to now run the SQL Server 2008 R2 Express SP1? Is there an upgrade to MS Access 2007 which will allow me to use SQL Server 2008 R2 Express?

Sharepoint 2003 + Windows Terminal Server 2008 + Office 2007 problem?

Hi, We have a customer where some users runs Windows Terminal Server 2008 with Office 2007. They open documents from a Sharepoint 2003 site. When they open an excel sheet they get a button in the document which says "Server workbook: Click edit workbook to edit this workbook" and there is a button: "edit workbook" The users clicks "edit workbook" but get an error message saying there is no connection to the server.  They are able to edit the document, but not save it back to the sharepoint site. Most other users work on their local computer with Office 2003. Here, everything works fine and there are no button/message stating its a server workbook.   Is this caused due to some compatability issues between Sharepoint 2003 and Office 2007?   Best regards, Thomas Hansen ErgoGroup AS

Problem configuring SSRS 2008 with sharepoint 2007 on Windows server 2008

Hi All - I am unable to integrate SSRS 2008 with sharepoint 2007. I am new to this part please correct em if am wrong. I have 2 servers one for SQL and one for MOSS MOSS server. Windows server 2008 MOSS 2007 .Net 2008 with basic sql 2005 which comes default WSP builder Everything is working fine SQL server: Windows Server 2008 SQl server 2008 and configured SSRS BIDS. When I am trying to install SSRS plugin for MOSS it is not accepting on both the machines. Please help me if am missing something here. I configured sucessfully on one server but havent done on multi server environment. please help me.   Thanks Saritha    saritha k

Upsizing Database from Access 2007 to SQL Server Express 2008

I have recently downloaded and installed SQL Server Express 2008 on to my Windows XP SP3 machine, I have a large MS Access 2007 database (.accdb) I wish to export out to SQL Server. I am using the 'upsizing wizard' that is found on the 'Database Tools' tab of Access 2007. Clicking on the 'Database Tools' tab and then on the 'SQL Server' button starts the Upsizing wizard. On the first page of which I select the 'Create new database' option, the next page asks 'What SQL Server would you like to use for this database?' it already has '(local)' entered into the text box, so I leave that unchanged, I tick the trusted connection box and name the new SQL Server database and then click 'Next'. But I get the following error (it appears in a Windows information message box):

Title: Microsoft SQL Server Login
Message Text:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(Connect()).
Connection failed:
SQL State: '08001'
SQL Server Error: 17
[Microsft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

PS. I have successfully used the upsizing wizard to export the same Access database into SQL Server Express 2005 on another machine.

What am I doing wrong this time?

exporting data from table on sql server 2008 to Ms access 2007



i wana know how can i export data from a table  to access through a sql job

i am using sql server 2008 and office 2007




Export to Excel 2007 Problem - SQL Server 2008



Hi! I did a quick test to dump data into an Excel spreadsheet. Everything worked fine, but when I created a job to do this for me and then run it, I get this error:


Date  10/8/2008 4:24:25 PM
Log  Job History (ExportClientInfoToSpreadsheet)

Step ID  1
Server  OHI0056
Job Name  ExportClientInfoToSpreadsheet
Step Name  CreateExcelClientExport
Duration  00:00:00
Sql Severity  0
Sql Message ID  0
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Executed as user: OHI0056\SYSTEM. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.1600.22 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Option "12.0;HDR=YES;" is not valid.  The command line parameters are invalid.  The step failed.


Here is the script of the job. Any ideas what is going on?





USE [msdb]


/****** Object: Job [ExportClientInfoT

Connectivity problem between MS Access 2007 and SQL Server Express 2005


I have linked my tables, in SQL Server Express 2005, to MS Access 2007 and created a File DSN named LocalSQLServer.dsn. Both the front end (MS Access 2007) and the back end (SQL Express 2005) are on the same machine.

I am trying to check an open connection , and for that I have written a module -- Function CheckConnection(strFileDSNName As String, _

      strDBName As String, Optional strUN As String, Optional strPW As String) As Boolean

strConnect = "Provider=ODBC" & _

";FILE DSN =" & strFileDSNName & _

";Database=" & strDBName


I have created a form with a command button to check the connection, but it is giving the following error

"-2147467259; Method 'Close Connection' of Object'_CurrentProject failed


I am not able to figure it out, if the Connection Parameters that I have taken are correct or not. Please help me in this.



2008 server Access database connecting win7 workstation with ODBC link works extremely slowly


I have an Access database on a 2008 server (64 bit), and notebooks with 32 bit win7. The program using the mdb is written in delphy, and running on my notebook, and working on data extremely slow. What can be the problem?

The database connector is an ODBC linked network drive.

On that same configured PC with XP it works normally.


SSMA for Access 2007 SQL Server 2008 Unable to Register

I have downloaded and installed the SSMA 2008 for Access 4.2 QFE1.exe on my Windows XP and Office 2007 Ultimate laptop, and tried to use it, but when the License Management pop-up opens and I click on the license registration page link on Step 1 of the instructions, IE opens and goes to the RegSysProfileCenter and displays the error message "An unknown error occurred while processing your request. This is most likely due to a connection failure. Please try again."  Why is this happening.  What's wrong with the website/page.  An "unknown error" means there's no known cause.  What's happening?  I keep trying over and over and over...  I've also tried to use it on my other Vista machine, but it keeps wanting to install Access 2010 runtime files (Office 2007 is installed) which it can't download - so it fails there too.  This is really getting frustrating.  Please show me you are truly "Microsoft" and NOT "Microcrap", which is a growing trend...  Thanks.

Trying to add a linked server - SQL Server 2008 Express Edition to Access 2007

OS: Vista SP1
SQL Server 2008 Express Edition installed locally
Office 2007 installed locally

I'm trying to add a linked server to a named instance of the SQL Server 2008 that links to an Access 2007 database.

I've spent a couple of hours trying this now - many, many different ways.

Always seem to end up back at Error 7303 - http://msdn.microsoft.com/en-us/library/aa226395(SQL.80).aspx

I've tried all versions of Access database file 2000, 2002-2003, 2007
I've tried to use different drivers -

Office 12 for Access driver

I've tried to use the upsizing wizard from Access - no good, cannot get it to connect to the SQL Server 2008 Express Edition instance

Whichever way I try it I end up with this message/error:

the upsizing wizard only works with microsoft sql server versions 6.50 sp5 or higher

I've read: http://support.microsoft.com/default.aspx/kb/838594 and followed it - same error.

I really want this to just work. Like it should already.

Any suggestions appreciated.

Matt Haddon

Linked Server From SQL 2008 R2 (32 or 64 bit) TO Access 2007 mdb


I have what I thought was a simple task.  Create an OLEDB connection to an Access2007 MDB data file within SQL Server 2008 R2.  Here's my environment:

    Windows 7 64-bit OS
    SQL Server 2008 R2 Standard 64-bit
    Office 2010 32-bit (installed 32-bit because all legacy data needed to be ported)

My first attempt was within SQL Server's Management Studio by navigating to Server Objects - Linked Servers and right-click selecting New Linked Server...
First indication of some trouble began when I didn't see an ODBC Provider for my Access database.  After trying a few options, I turned to Google expecting a quick resolution.  Here is the beginning of 5 days of going round in circles!!!

Eventually I found some links that led me to potential solutions:

Problem in Installation of SQL Server 2008


Dear All,

I have Sqlserver2008 setup.

It is work in wndows Xp.

But I have to Install it on Windows 7 ,during installation process it requires some patch and I am unable to get it.

Plz some buddy can help the same.


Rakesh gupta

Problem opening dotnet 1.1 projet on 2008 web server


 We recently upgraded to a 2008 development web server and I installed all the stuff to get dot net 1.1 app working on the server.  I can host them and they work.  My problem is that when I try to open one in VS 2003 I get the following error.  I set up the web server the same as my previous one, and I think a long time  ago I got this error when we set up current one but do not remember how to resolve it.  So I moved my project files from the old server to the new server and then I get these errors. 


The web Server reported the following error when attempting to create or open the Web Project located at the following url: Http://localhost:/MobTracker. 'Http/1.1 500 server error

How can Install Office 2007 on Windows server 2008 R2 64 bit machine in WSS 3.0

I have  64 bit machine  and Windows server 2008 R2 has installed. i have successfully install WSS 3.0  , but  when i tried to install  office 2007 ,  one  error  has  come  "OS is not compatible "  i thought  it was asking  for 64 bit office  2007   and i go through the  google and R&d find no 64 bit office is available ,  i have used   excel .dll in my custom code  so my problem is that   how can  install office 2007  on 64 bot OS 2008 r2  machine .  if anyone can help   me  , please let me know . thanks in advance

VS Studio WorkFlow Failed on Start in MOSS 2007 ON Windows Server 2008 64 Bit


I have created a sequential sharepoint workflow in WSP Builder in my Sharepoint Server and Deployed it.

But the workflows says Failed on start and retrying ans stops there forever says couldn't start the workflow.

I repaired .NET Framework 3.5sp1 installed

 tried this

 1. I tried again, in C Prompt:  Lodctr "c:\Windows\Microsoft.Net\Framework\v3.0\Windows Workflow Foundation\perfcounters.ini"  it says initiation file cannot be found

2   i again tried this C prompt Lodctr /R "c:\Windows\Microsoft.Net\Framework\v3.0\Windows Workflow Foundation\perfcounters.ini"

it says reloaded  from the system restored

i am still getting the same error.

When i manually go to my  C:\Windows\Microsoft.NET\Framework\v3.0\Windows Workflow Foundation i can see a folder for SQL that's it nothing more than that..

I dont know what is happening i am trying it from 2 days and still no solution.

any ideas would be real great help for me.

My system is 64 bit , Window server 2008, MOSS SP2, Visual Studio 2008 Version 9.0.30729.

.Net Framework 3.5 sp1

and WSP builder the one from Codeplex for 64 bit machines

Administrator and Developer Guide to Code Access Security in SharePoint Server 2007

Explore configuration options, get best practices for managing CAS in SharePoint environments, and walk through a complex CAS scenario.

Linked Server to access Excel 2007



I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.

Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
Exec sp_dropserver 'AB2','droplogins'

Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges


EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1

The DisAllowAdHocProcess in
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend