| Author |
 Topic  |
|
Podge
Support Moderator
Ireland
3775 Posts |
 Posted - 30 August 2007 : 10:53:09
|
In SQL 2000 I can write an SP which takes a number of parameters. In MySql I can use this syntaxINSERT INTO beautiful (name, age) VALUES ('Helen', 24),('Katrina', 21),('Samia', 22),('Hui Ling', 25), ('Yumie', 29)";
Is there a neat way of doing multiple inserts into the same table in Access (as opposed to looping through a few hundred insert statements) ? |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 30 August 2007 : 11:03:36
|
| Doesn't that work in Access? I could have sworn that I've seen something similar in one of my other apps... |
 |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 30 August 2007 : 12:43:46
|
There is a better way, although through a trick...
Create a similar MySQL or SQL Server DB. Add the records to that DB. Define an ODBC data source for that DB and then import the table or tables from Access. Even if the table structure is not the one you want, you can then use an append query to move the data from the "incorrect" access table to the good table.
Of course, this only makes sense if you want to do this offline. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 30 August 2007 : 12:48:36
|
| Do it as a loop? What interface are you using? Could you use DTS, or is it ADO/DAO/RDO/etc? |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
Posted - 30 August 2007 : 12:55:15
|
I saw somewhere that you could access the mdb file via a MySql odbc driver which supported the MySql syntax. Not really an option if its for a Snitz mod.
Instead I think I can have the queries run in the admin area once every so often instead of after each post. It might be better that way anyway.
Quick question: Whats F_STATUS used for in the FORUM_FORUM table ? Is it safe to assign a status to it like this ?
Status 2 for Recycle Bin
Status 3 for SPAM
Status 4 for whatever |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 August 2007 : 06:57:46
|
quote:
Originally posted by Podge
I saw somewhere that you could access the mdb file via a MySql odbc driver which supported the MySql syntax. Not really an option if its for a Snitz mod.
Instead I think I can have the queries run in the admin area once every so often instead of after each post. It might be better that way anyway.
I guess you could chang a bit of code to execute a dbs file whenever you wanted... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Podge
Support Moderator
Ireland
3775 Posts |
Posted - 31 August 2007 : 08:02:15
|
After thinking about it for a while there is no other option. I'm going to need to do a select first anyway to see if a row already exists and if it does get a value from one of the columns. If it doesn't already exist I can just insert it. One other thing which I'm sure is easy for you (my sql sucks) is how to write this query;
Select T_MESSAGE from FORUM_TOPICS and R_MESSAGE from FORUM_REPLY where FORUM_FORUM.F_TYPE = 2
I don't need topic id's or anything else just one column of all the topics and replies in forums with an F_TYPE of 2. I just can't get it right. |
Podge.
The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)
My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board
Warning: The post above or below may contain nuts. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 August 2007 : 08:51:14
|
This should handle it, as long as you are running at MySQL 4.x+
Select T_MESSAGE from FORUM_TOPICS T INNER JOIN FORUM_FORUM F ON T.FORUM_ID=F.FORUM_ID where F.F_TYPE = 2
UNION
SELECT R_MESSAGE from FORUM_REPLY R INNER JOIN FORUM_FORUM F ON R.FORUM_ID=F.FORUM_ID where F.F_TYPE = 2
Do you need the message or do you just need to know if there is such a topic in such a forum? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 31 August 2007 : 08:59:19
|
Here is a much more efficient version, if you just interested in finding if a record exists:
SELECT 1 FROM FORUM_TOPICS WHERE EXISTS
(Select TOPIC_ID from FORUM_TOPICS T INNER JOIN FORUM_FORUM F ON T.FORUM_ID=F.FORUM_ID where F.F_TYPE = 2
UNION
SELECT REPLY_ID from FORUM_REPLY R INNER JOIN FORUM_FORUM F ON R.FORUM_ID=F.FORUM_ID where F.F_TYPE = 2)
Not sure why you're testing for a reply, cause there can be no replies if there aren't topics... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 31 August 2007 : 09:01:23
|
| I would guess its because a reply may be bad in a good topic. Likewise a topic could be bad from the get-go. |
|
|
|
Topic |
|
Topic Locked
