I have a project that is going to use ADP as a front end and SQL server as
the database. Now I need to import flat text files to a temptable in SQL. I
been reading that I should use DTS packages but unfor, from what I have
heard, DTS can only be triggered if scheduled. I am creating a form where th
e
user will have to trigger it. Wonder if there are any other suggestions out
there. Right now, what I created is a macro in ADP that will call a batch
file on the drive that will open up an MDB database and import the files tha
t
way onto sql. Few problems I have right now
1) I have a form where the user clicks on "Locate files" and a subform
opens. It shows all the files in that particular directory. Right next to
each files there is an upload check box. If it check box is ckecked, then
files will be uploaded. There are 2 drop downs in the subform where user wil
l
have to fill in. What I need is if
the check box has been checked but either or both drop downs hasn't been
filled that an error message should pop up before procedure can execute. I a
m
tryin to have to msg box be like a list of all the files that is missing a
drop down. My message box right now only tells one file name but not the
others, if applicable.
for instance, if there are 7 files tha are to be uploaded and 2 of them i
didn't have any drop downs for, i have a message to say "file name text 1 an
d
text 8 are missing..."
2) everytime I open up that database, I get SQL server login error
Connection Failed
SQL state '28000'
Login failed for user(null). Reason, not associated with trusted SQL server
connection.
If i click on "ok" connection pops up and I have to manually enter the
infor. Can I somehow add this in a vb code so user wont' have to keep
manually typing info.
3) Currently, after the import, the MDB kills itself and you are back in the
ADP. Wonder if before the MDB kills itself, opens up an form in ADP and then
kills. THe form is a summary form that shows all the data that was just
imported.(if any other way, suggestions are welcomed).
please help"Justin" <Justin@.discussions.microsoft.com> wrote in message
news:F4E455A7-49AC-4E29-8178-9FE43071C905@.microsoft.com...
>I have a project that is going to use ADP as a front end and SQL server as
> the database. Now I need to import flat text files to a temptable in SQL.
> I
> been reading that I should use DTS packages but unfor, from what I have
> heard, DTS can only be triggered if scheduled. I am creating a form where
> the
> user will have to trigger it. Wonder if there are any other suggestions
> out
> there. Right now, what I created is a macro in ADP that will call a batch
> file on the drive that will open up an MDB database and import the files
> that
> way onto sql. Few problems I have right now
> 1) I have a form where the user clicks on "Locate files" and a subform
> opens. It shows all the files in that particular directory. Right next to
> each files there is an upload check box. If it check box is ckecked, then
> files will be uploaded. There are 2 drop downs in the subform where user
> will
> have to fill in. What I need is if
> the check box has been checked but either or both drop downs hasn't been
> filled that an error message should pop up before procedure can execute. I
> am
> tryin to have to msg box be like a list of all the files that is missing a
> drop down. My message box right now only tells one file name but not the
> others, if applicable.
> for instance, if there are 7 files tha are to be uploaded and 2 of them i
> didn't have any drop downs for, i have a message to say "file name text 1
> and
> text 8 are missing..."
> 2) everytime I open up that database, I get SQL server login error
> Connection Failed
> SQL state '28000'
> Login failed for user(null). Reason, not associated with trusted SQL
> server
> connection.
> If i click on "ok" connection pops up and I have to manually enter the
> infor. Can I somehow add this in a vb code so user wont' have to keep
> manually typing info.
> 3) Currently, after the import, the MDB kills itself and you are back in
> the
> ADP. Wonder if before the MDB kills itself, opens up an form in ADP and
> then
> kills. THe form is a summary form that shows all the data that was just
> imported.(if any other way, suggestions are welcomed).
> please help
There are a number of options for executing DTS packages. They don't have to
be scheduled and they can be run from your code or using the DTSRUN.EXE
executable. See Books Online for details of DTSRUN. See the following link
for other options:
http://www.sqldts.com/default.aspx?104
Depending on the format of your source file another possibility could be to
use BCP or BULK INSERT to load the data. Again, see BOL for details.
For the rest of your questions you might get more help in an Access group.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
