I did the same thing.
A. Link the Emerald DB into your access DB
B Load 3 queries in access (just paste them into a new query sql)
C Set your defaults as desired first in the queries or change the values =
later
C Run 'Append Master Accounts' first then run 'Append Sub Accounts' last
1. Append Sub Accounts
INSERT INTO dbo_SubAccounts ( CustomerID, FirstName, LastName, =
PhoneHome, Login, Shell, PhoneWork, Description, AccountType, Password, =
saExpireDate, Extension, Active, TimeLeft, LoginLimit, SignDate, =
LastModifyDate, LastModifyUser, Cost, Email, saCurrent )
SELECT [Sub Account Pre Load].CustomerID, [Sub Account Pre =
Load].FirstName, [Sub Account Pre Load].LastName, [Sub Account Pre =
Load].PhoneHome, [Sub Account Pre Load].Login, [Sub Account Pre =
Load].Shell, [Sub Account Pre Load].PhoneWork, [Sub Account Pre =
Load].Description, [Sub Account Pre Load].AccountType, [Sub Account Pre =
Load].Password, [Sub Account Pre Load].saExpireDate, [Sub Account Pre =
Load].Extension, [Sub Account Pre Load].Active, [Sub Account Pre =
Load].TimeLeft, 1 AS Expr2, [Sub Account Pre Load].CreateDate, [Sub =
Account Pre Load].LastModifyDate, [Sub Account Pre Load].LastModifyUser, =
0 AS Expr3, [Sub Account Pre Load]![Login] & "@pop.intertrek.net" AS =
Expr4, 0 AS Expr1
FROM [Sub Account Pre Load];
2. Sub Account Pre Load
INSERT INTO dbo_MasterAccounts ( FirstName, LastName, Company, Address1, =
Address2, City, State, Zip, PhoneHome, PhoneWork, PhoneFax, CreateDate, =
StartDate, maExpireDate, Extension, Active, Comments, LastModifyDate, =
LastModifyUser, PayMethod )
SELECT MasterAccounts.FirstName, MasterAccounts.LastName, =
MasterAccounts.Company, MasterAccounts.Address1, =
MasterAccounts.Address2, MasterAccounts.City, MasterAccounts.State, =
MasterAccounts.Zip, MasterAccounts.PhoneHome, MasterAccounts.PhoneWork, =
MasterAccounts.PhoneFax, MasterAccounts.CreateDate, =
MasterAccounts.StartDate, MasterAccounts.maExpireDate, =
MasterAccounts.Extension, MasterAccounts.Active, =
MasterAccounts.Comments, MasterAccounts.LastModifyDate, =
MasterAccounts.LastModifyUser, " Invoice" AS Expr1
FROM MasterAccounts;
3. Append Master Accounts
SELECT dbo_MasterAccounts.CustomerID, MasterAccounts.FirstName, =
MasterAccounts.LastName, MasterAccounts.PhoneHome, SubAccounts.Login, =
SubAccounts.Shell, SubAccounts.PhoneWork, SubAccounts.AccountType, =
SubAccounts.Description, SubAccounts.Password, SubAccounts.saExpireDate, =
SubAccounts.Extension, SubAccounts.Active, SubAccounts.TimeLeft, =
SubAccounts.LoginLimit, dbo_MasterAccounts.CreateDate, =
dbo_MasterAccounts.LastModifyDate, dbo_MasterAccounts.LastModifyUser
FROM (MasterAccounts INNER JOIN dbo_MasterAccounts ON =
(MasterAccounts.LastName =3D dbo_MasterAccounts.LastName) AND =
(MasterAccounts.FirstName =3D dbo_MasterAccounts.FirstName)) INNER JOIN =
SubAccounts ON MasterAccounts.CustomerID =3D SubAccounts.CustomerID;
Robert McGuire
***************************************
*** InterTrek Technologies, LLC ***
*** Internet Services ***
*** ***
*** UNLIMITED ***
*** INTERNET ACCESS ***
*** ONLY $14.99 MONTH ***
***************************************
www: http://www.intertrek.net
Address: 61 Grandview Ave.
Wallingford, CT 06492-4628
Phone: (203)284-0552
Fax: (203)284-0552
Email: rmcguire@intertrek.net
-----Original Message-----
From: L. Sun [SMTP:sun@larkom.net]
Sent: Monday, April 21, 1997 4:52 PM
To: emerald@emerald.iea.com
Subject: Emerald setup question.
Hi, Dale:
I have decided to try emerald. Right now I got the NTmail setup,
Soft*Deposit is coming ( I bought it). I wonder where I can find=20
the document to configure the emerald to work with NTmail and=20
Soft*Deposit. And how I can transfer the MS Access database file
into Emerald.=20
I can not use following statements to insert subaccounts, and =
Masteraccounts
insert into SubAccounts (AccountID, CustomerID, Login, Shell, FirstName,
LastName, PhoneHome, PhoneWork, AccountType, Description, Password,
saExpireDate, Extension, Active, TimeLeft, LoginLimit)
values ('1', '1', 'test', NULL, 'test', 'user', NULL, NULL, 'PPP', =
NULL,
'testtest', NULL, NULL, '1', '-9999', '5');
insert into MasterAccounts (CustomerID, FirstName, LastName, Company,
Address1, Address2, City, State, Zip, PhoneHome, PhoneWork, PhoneFax,
CreateDate, StartDate, maExpireDate, Extension, Active, Comments,
LastModifyDate, LastModifyUser)
values ('1', 'test', 'user', NULL, NULL, NULL, NULL, NULL, NULL, =
NULL,
NULL, NULL, NULL, NULL, '1999-01-01 00:00:00', '0', '1', NULL, NULL, =
NULL);
Can you help me out, so I can insert our data into Emerald.
I appreciate all your help.=20
=20
Thanks!
L. Sun -:) =20
Webmaster and System Administrator=20
-------------------------------------------------------------------------=
---------Larkom Your Link to the Rest of the WorldTel: (714)492-2938 Email: sun@larkom.netFAX: (714)492-2578 URL: http://www.larkom.net
---------------------------------------------------------- Emerald Mailing List listserver@emerald.iea.com