Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
craigfw
Starting Member
8 Posts |
 Posted - 2004-08-11 : 10:12:51
|
I'm having to reload some tables in a MS SQL Srvr from an Access database, using DBArtisan Pro, 7.3.1, with no automation necessary. I have been using bcp and it works, but I'm wondering if anyone can provide any information on working with Access as I haven't used it before. Also what datatype would you reccomend in a datetime field in Access to SQL Srvr?Thank you |
|
|
craigfw
Starting Member
8 Posts |
Posted - 2004-08-11 : 12:12:50
|
| Forget the Date/Time question as it became a no-brainer. I haven't been a DBA for 4 years and I'm starting up again. I do need to find out how to bcp a single from Access into a MS SQL Server is anyone knows (if it's possible). I'm checking the constrains (which I don't belive there are many of any). Thanks again. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-11 : 13:12:36
|
| Can you use DTS instead of BCP? That will transfer natively from Access to SQL - or am I on the wrong wavelength?Kristen |
|
|
|
craigfw
Starting Member
8 Posts |
Posted - 2004-08-11 : 15:01:21
|
| I've just tried using DTS but it errors out saying, "Error Source MS Database Engine: Error Description, cannot start your application. The Workgroup file of missing or opened exclusively by another user. Context: Error duing the initalizing of the provider."I have the Access file either on a Read-only CD or it has been copied to a directory on the C: drive. Have you seen this before? |
|
|
|
craigfw
Starting Member
8 Posts |
Posted - 2004-08-13 : 11:10:27
|
| I really need some help on this. I now have the Access tables under my Documents and DTS doesn't recognize the 13 Access tables in my Cellphone database. This db was created by someone who didn't understand relational and I have to go through evey column in everytable between Access and SQL Srvr and make sure the datatypes were compatible and remove the reserved words. I haven't ever been able to load the tables in the SQL srvr.CellPhone db ever. If anyone has any ideas on how to approach this, I'm open to any and all suggestions (dba humility). The SQL Server is on the network and we are using Window trusted authentication, I'm either using DB Artisan or SQL Srvr Enterprize and can connect with both tools. The error I get when trying to connect and import the db.tables with DTS is: "Error Sourc: Microsoft JET Database Engine. Error Description: Cannot start your applicatin. The workgroup information file is missing or opened exclusively by another user. Context: Error during initialization of the Provider." I have the Access db.tables on my local machine and permissions are wide open. I can open Access and view all the table and I'm administrator on the SQL Srvr. Please help. Should I be importing the data as text insted of an Access DB and setting delimiters and what would they be? Help, help, help, etc. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 08:29:34
|
| Sorry, I don't have much knowledge of Access.Kristen |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-15 : 10:16:22
|
| Can you open the Access database by itself (ie just using Access)?. The error sounds like the tool you're using can't open the mdb file. If you also can't open it in Access itself, then I'm afraid you've got a problem. The workgroup file referred to in teh error message is Access's method of securing stuff. However, if you can open the db by itself, you could use the Access upsizing wizard to upload it to SQL. You could upload it into some staging tables, where you can do your manipulation and move into the production tables. |
|
|
|
craigfw
Starting Member
8 Posts |
Posted - 2004-08-20 : 12:17:21
|
| Thanks for the suggestions. I am such a weenie at Access. I was able to save the access tables as *.txt and then using DTS bcp the tables into the new tables in the SQL Srvr. Since I don't now much on anything about Access it took me a while to find where and how to do this. Thanks to the both of you for you assistance.Craig |
|
|
|
|
|
|
|
|
Access bcp in MS SQL Server - Best Practices?