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 |
|
andru
Starting Member
10 Posts |
 Posted - 2005-06-30 : 17:47:37
|
| I am using Access VBA to work on my SQL Server 2000 database.I want to create a complicated piece of SQL code to create a large and complex table. I need to create a table as if when I designed a query I run into the maximum 256 queries error.So I wrote some code to generate a stored procedure, that repeatedly inserted records in a table. And again I ran into a problem this time, my SQL string to create a stored procedure was 180000 characters long and the maximum possible length of a SQL string using DoCmd.RunSQL is 32000 characters.This time I decided to create the table and then run chunks of SQL less than 32000 characters at a time, unfortunately again this didn't work and all that happened was a empty table was created. I guess the reason being that the Access RunCmd.RunSQL method counldn't handle inserting records with such a long and complicated SELECT.So I tried a new tack and this time, I created a table. Then a stored procedure using DoCmd.RunSQL and I called the stored procedure which inserted my records and did this repeatedly. It worked BUT I had the hassle of having to sit and repeatedly click OK to the message "The stored procedure executed successfully but did not return records".Surely there must be a better way?Yours in hope,Andru |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-30 : 19:44:51
|
| Yes, there is a better way, don't use Access VBA to do this. |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-06-30 : 19:48:20
|
| Docmd.SetWarnings Off....or something like that will get rid of the UI message..And thanks for the flash back...I needed an excuse to drink myself stupidDavidMA front-end is something that tries to violate a back-end. |
|
|
|
andru
Starting Member
10 Posts |
Posted - 2005-07-01 : 06:21:14
|
| Thanks for DoCmd.SetWarnings FalseBut guess what I get no results again and when I ran the SELECT for an INSERT, I got a Timeout.HELP!!!I am now really up against a deadline.Andrew |
|
|
|
andru
Starting Member
10 Posts |
Posted - 2005-07-01 : 07:56:48
|
| OK, I've solved the Timeout by using Tools/Options/Advanced and setting the wait to 300 secs.Also I have used ADO to call the stored procedure, see below: Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd .ActiveConnection = CurrentProject.Connection .CommandType = adCmdStoredProc .CommandText = ProcedureName .CommandTimeout = 0 .Execute End With Set cmd = NothingHow do I get Access to wait until this procedure is complete before moving on???Please help.Andru |
|
|
|
|
|
|
Creating a complicated query