November 20, 2003 at 9:44 am
I don’t know if this is a SQL problem or an Access problem, but here goes…
I am trying to create a dynamic process that copies data in tables in an Access 2000 database to corresponding tables in SQL Servier 2000. I want this to run nightly as a Windows task where the Access database wakes itself up and runs the process, then closes itself.
There are lots (more than 200) of tables in the Access database, and new tables appear and existing table structure changes fairly frequently. Rather than use DTS, which I’d have to be modify continually, my plan is to write code in Access to do the following:
1) Loop thru the existing SQL Server tables and truncate them all
2) Loop thru the Access tables, record their structure, then create or drop and recreate any new or changed SQL server tables so they match up with the Access tables
3) Loop thru the Access tables and create a pipe-delimited text file containing their data, then paste the text file to a directory on the SQL Server box
4) Loop thru the text files and bulk insert their contents into the appropriate SQL Server table
My problem is that my T-SQL code for the bulk insert process works from QA, but not when I run it from a module in Access. Here is the code:
Bulk Insert DatabaseName.dbo.TableName
From 'E:\AccessTables\TableName.txt'
With ( FIELDTERMINATOR = '|' )
In the Access module I put this string into a string variable called “strSql” and try the following:
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandType = adCmdTable
.CommandText = strSql
.CommandTimeout = gintExtractTimeout
.Execute
End With
I get a VB runtime error:
Error Number: -2147217900
Error Description: Incorrect syntax near the keyword 'Bulk'.
Error Source: Microsoft OLE DB Provider for SQL Server
I also tried encapsulating this into a stored procedure with the tables name as a parrameter (which also runs fine from QA), and running that from Access as an adCmdStoredProc ADODB.Command. When I try that I get the error:
Error Number: -2147217900
Error Description: Unclosed quotation mark before the character string 'E:\AccessTables\TableNa'.
Error Source: Microsoft OLE DB Provider for SQL Server
Something tells me this problem is related to the single quotes in the T-SQL statement. I tried running the following from QA to get rid of them, but it didn’t work:
Declare@SourceTextFilevarchar(50)
Declare@Delimiterchar(1)
Set@SourceTextFile ='E:\AccessTables\TableName.txt'
Set@Delimiter ='|'
Truncate Table ZOHMCDev.dbo.zApollo_Cath_Complications
Bulk Insert ZOHMCDev.dbo.zApollo_Cath_Complications
From@SourceTextFile
With ( FIELDTERMINATOR = @Delimiter )
I know this is a lot to plow thru, so I appologize in advance and appreciate any insight you can give me.
Regards
K2
(PS – as an aside, if anyone can tell me how to programmatically change the delimiter in the Access DoCmd.TransferText acExportDelim function from a comma to something else, I would be grateful – its giving me fits.)
thanks
November 24, 2003 at 7:32 am
I would do it the other way around.
1. Sql Server is a Service so you don't have to "wake it up"
2. You can create a Linked server to the Access DB (Now you will have access to live Data, no intermediate files)
3. To Loop through all your sqlTables is fairly straight forward using "sp_MSforeachTable"
4.Using sp_table_ex you can get All your tables needs
5. and the creation would be a simple SELECT INTO ... FROM ... Linked Server Table
HTH
* Noel
November 24, 2003 at 8:57 am
Thanks, noeld, I'll talk to my DBA about linking to the Access database - and thanks for pointing out "sp_MSforeachTable" - very cool!
regards
k2
November 26, 2003 at 7:43 am
Number one didnt look that bad to me. A minor variation would be to link the SQL tables in after you create/update/verify, then just do a straight append. Probably not quite as efficient as bulk insert, but not horrible either.
The pipe delimited idea - remember to check for embedded pipes in your data. Rare, but not impossible.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply