• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 607
  • Last Modified:

Access 2010 transfertext Issue

I have a database that I am migrating from 2002 to 2010 Access.  The Access project uses a sql database.

From the database window I try to use the transfertext buttons from the menus to export a table to a file.  I actually have code in visual basic to do the same thing and neither works.  When I do the transfer text I get an error that says:

Connection Failed
SQLState: '01000'
SQLError: 161
[Microsoft][ODBC SQL Server][DBNETLIB]ConnectionOpen(Connect())
Connection Failed
SQLState '08001'
SQL Server Error 17
[Microsoft][ODBC SQL Server][DBNETLIB]SQL Server does not exist or access denied

The project opens fine with the connection I have provided and I can see all of the tables and queries in the database window but cannot export any of the tables.

Any ideas
0
bmusante
Asked:
bmusante
  • 6
  • 4
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you post the code you're using?
0
 
bmusanteAuthor Commented:
I don't think you need the code - I can't do a transfer text on the table from the database window - I just highlight the table in the database window of the adp and select external data, then text file and the wizard opens to export the file.  I use the default location and name and hit next select the defaults and finish.  That is when I get the error.

I have done this a thousand times in 2003 but 2010 does not like something.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The error is pretty well self-explanatory - you either cannot access the server, or you don't have permissions to do what you want to do.

I assume you can open and browse the tables, so you obviously can access the server. From there, I'd have to assume there is a permissions issue. I'm not aware of any changes in 2010 that would affect this.

Are you using an ADP, or linked tables?

Did you create a new, blank database in 2010 and import everything into that new database? Quite often simply doing this will eliminate some of those odd issues. Note you can keep the .mdb/.adp format in 2010 if need be.

Can you build a new database, and link to the server, and try the tranfer from that new database?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bmusanteAuthor Commented:
I am using an ADP connected to a sql database running on my computer with sqlexpress.  I have created a new 2010 adp and connected to a database using a SQL login that I created specifically for this application.  I also created a user by the name name as the login and mapped the user to that login.  I have imported all of my forms, reports and modules from the old adp into this new one.

I have successfully connected to the database using the new login so that is all ok.  I have given the login all of the permissions that I can think of but the transfertext still does not work.

Any ideas
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd still try my last suggestion, just to see if things are getting flunky with the ADP environment (which, by the way, is gone in Access 2013):

Build a new .mdb file, and link the tables in the SQL Server database. Then see if you can use the Transfer Text feature from that database. If you can, then you'd have to think that something is up with the ADP format.

If not, then it would almost have to be permissions.
0
 
bmusanteAuthor Commented:
I can try that but I do think that it must be permissions of some sort.  By the way, will we have to use an mdp in 2013 and just link tables and queries from sql?  Isn't that less efficient?  But maybe not so complex?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm confident that it's a permissions issue, but it's impossible for us to determine the exact fix without knowing more about the error.

I'd start by allowing the user Full Control of the SQL Tables, and then pare off the permissions from there.

2007 - 2013 use the .accdb format, although they can run .mdb files. I'd use the newer .accdb format, however.

I'm not sure it's less efficient. IMO there is little difference in ADP and linked tables, performance-wise.
0
 
bmusanteAuthor Commented:
The connection login has full control of the database and the windows user who is me also has full control of the database.

I take it you are a consultant - do you do small jobs.  If I can't figure this and a couple of other issues I may need to hire you.  What is your hourly rate.
0
 
bmusanteAuthor Commented:
I found the problem.  Very simple really although I don't know why Access could connect to the sql server but the utility that does the transfertext could not.  I had the database server in the data link connection as \sql express and it needed to be .\sql express.  Just that one dot did it.

Thanks to all who helped.
0
 
bmusanteAuthor Commented:
I found the problem by researching the sql logs and documentation.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now