April 9, 2008 at 1:15 pm
Hello, all, -- I am new to SQLExpress, but good experience with MS-Access 2003. I am unsure about the ODBC connection to the SQL-db.
The exported table does not appear in PROJ001.MDF (SQL-SERVER-EXPRESS).
Here are my steps.
In MS-Access, I select 'tblITEMS' and select EXPORT and choose ODBC-databases from the file-types combo.
I enter 'tblITEMS' in the EXPORT-ITEM prompt.
I am presented with a 'Select Data Source' dialog where I navigate to my PROJ001.MDF folder where there is a proj001.mdf.DSN file and select it. This DSN contains this information...
[ODBC]
DRIVER=SQL Server
UID=XXXXX
Trusted_Connection=Yes
WSID=..mycomputername..
APP=Microsoft Office 2003
SERVER=..mycomputername..\SQLEXPRESS
Description=DSN_PROJ001SQL
There is a delay of about 10-seconds and the MS-access window becomes active again.
I then close MS-access and navigate to MS-WEBDeveloper and open my PROJ001- web-project and open the PROJ001.MDF database AND there is no exported table: tblITEMS.
So -- what may be wrong here?
I may be in the wrong forum, since this question involves both MS-Access and SQLExpress.
April 24, 2008 at 5:46 pm
It appears that you are trying to write this into the sql data file [.mdf]directly. If so, that is impossible. If I am wrong about that, digg this WAY down and stop reading. However, if not, read on...
Anything that ends up in an .MDF file is put there by SQL. In SQL you don't interact with files (like Access); you interact with the engine interface.
What you should be doing is establishing a connection to a database in an instance of SQL. The simplest (not the best) way to 'export' the table(s) is using the Access upsizing wizard. It is probably best to use sql Express as your target, certainly for testing.
However, it is more reliable to use the Sql Server Migration Wizard for Access. This will give you much more control and useful information for troubleshooting transfers that don't go smoothly. Try the upsizing wizard first, then use SSMA if it doesn't work out.
You also must have an instance of SQL running (I presume you do (or have) since you can see files with an .mdf extension). SQL Express should suffice for testing. For SSMA, you may also want to have pre-created a target database within the engine instance. It can be empty.
However, you can 'export' to an operational database container with pre-existing tables, etc.
BTW, once you have established a connection between and Access database and a sql database, you can transfer data between local tables (inside the .mdb) and linked 'remote' sql tables using standard DML (update, insert) commands available throught the QBE grid.
April 25, 2008 at 5:32 am
Thank you , Grasshopper, Scott -- you have clarified the situation.
Since the time that I posted the original post, I have discovered a number of things to assist me AND your post/reply is exactly on target (with what I learned -- see the SQL-Server forum for other answers on this subject).
Firstly -- the export wizard would "fail" to export into empty SQL-DB due to bad 'dates' in one of my tables -- dates like '12/16/207' (year is 207 when the Romans were building the aquaducts) -- evidently, SQL-SRVR does not like really old dates. I washed the data in ACCESS using VBA-code with my 'eyes' as a manual date-converter and successfully upsized.
Then after I upsized the MDB to the SQLEXPR-DB, I 'incorrectly' moved the MDF+LDF to my c:\WEB_PROJECTS\PROJ001\APP_DATA folder and tried to access the MDF as you stated I should not do.
After I moved the MDF back to the 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data' folder and established a connection to it I was able to 'export' other MDB-tables to the SQLEXPR-DB. I found that fiddling with the MDB-data (with queries and VBA-code) is an efficient way to 'condition' the data for SQL-DB.
I thank all who have properly instructed me on how SQL-SRVR works.
I did not ever get any QUERIES to export from MDB to SQLEXPR-DB, however -- any hints would be appreciated.
Thank...John
April 25, 2008 at 12:20 pm
Yup, out-of-range dates are a principal upsize killer.
In SQL2008, this is/will be somwhat less problematical as it will provide new date (and time) datatypes that will subsume all reasonable date ranges. Thus, if '1/1/207' is legit, it can make it in.
Glad to help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply