New user needs help!

[Guest]

trying to create ADP front end that will connect to company's SQL Server.
Need help in doing this since this is my first time.

1. Have an mdb form - would like to utilize it in ADP. I migrated the forms
and the tables and code over and am unable to make them work. I don't know if
the table have to be housed in SQL or can the form use a local table housed
in ADP? The error message states: "Run-time error '2757': There was a problem
accessing a property or method of the OLE object"

2. we will be specifying in a table which files on a drive to be uploaded to
a SQL table. Is there an easier way to upload off the drive? Is there a
pre-existing form we can use? The form points to the network drive, displays
all the files contained there and lets the user check off the files to be
imported to the SQL table.

Please advise! All help is appreciated.Thanks!

 

[Tom Ellison]

Dear Justin:

1.

There are no "local tables" with an ADP.

You can get the same effect by using the Computer Network Name as part of a
key in a table on the server. This is an exact equivalent. A table like
this is said to be "partitioned" among the computers accessing it. You need
only filter by the Computer Network Name (I choose that because it is
unique) to see only those rows pertaining to each computer.

Even better is to add another key column to identify the "instance" of the
application running. If a user opens two copies of the Access front end and
both use a local MDB table, they would confilct. Keying the rows by this
key ensures they are partitioned on this basis also. If you have an MDB
that doesn't preclude multiple instances, you could really get caught by
this!

For an "instance key" I save the hWnd of the initial form. This could be a
login form or the main "switchboard" or menu of the application. Don't
close this form, just make it Visible = False when you don't want to see it.
That way, it is guaranteed that this hWnd cannot be duplicated. It
therefore becomes an "identity" for each instance of the application. Save
it in a global variable for further use.

2.

A common term for this is "import." It sounds like you want to append rows
from a text file into a table.

So, a user will navigate to a folder somewhere and see a set of file names.
He will select one or more. Sounds like a multi-select list box.

Is it possible that the user will navigate to another folder and pick some
more files? Or are all the desired files in just one folder?

If they're all in one folder, you wouldn't need to save them in a table.
You could just iterate through the selections in the multi-select list box.
But putting them into a table sounds like a very good general solution.

Is there an easier way? You didn't tell that much about the way you're
doing it now. That makes it difficult to say whether there's an easier way.
Just working off a multi-select check box would be pretty easy if the folder
doesn't change.

Tom Ellison

 

[Robert Morley]

For an "instance key" I save the hWnd of the initial form.

If you're doing this server-side, wouldn't @@SPID be a better choice? Or is
there some problem with that that I'm unaware of? I'm still relatively new
to SQL Server (about a year).



Thanks,
Rob

 

[Tom Ellison]

Dear Robert:

I am specifically NOT doing this server side. Not that your suggestion
might not work.

If there were multiple instances of the same Access front end on a computer,
would each have it's own connection. I think so.

But how would it be better? Any simple thing, like a number, that is
guaranteed to be unique to an instance of the front end, would work. The
hWnd of the initial form would be such a value. You would have to compound
that with the Computer Network Name to be universally unique.

Now the SPID would be universally unique by itself. If it were not valuable
to know the Network Computer Name (as I have found it to be) then, when I
think of it, the SPID would be better. But knowing the Computer Network
Name is diagnostic for me when things created "temporarily" get orphaned.
The design for me has been to remove them when finished. A system crash or
crash of the front end sometimes orphans them. Being able to identify
problems with this happening has been useful, so the Network Computer Name
is a good thing to know.

Your thinking here is good. My experience leads me to prefer what I have
described, but you certainly have the right idea.

Tom Ellison

 

[Robert Morley]

Thanks, Tom. Just wanted to make sure there wasn't some hidden drawback to
SPID that I wasn't aware of.

I do agree with you, though, the network computer name is invariably useful,
and I make sure to log that and a number of other important pieces of
information for error-logging, user-tracking and the like.



Rob