LoginF connection problem - How To Connect Multiple Users to Online SQL Server Databases Using Microsoft Access and Web ASP


	Back to Access SQL Server Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

LoginF connection problem

Despina Karayanni

5 years ago

Hello Richard,
I'm trying to apply your instructions, however I have some problems with this.
First, when I try to connect through the LoginF (as I have adopted the security seminar Login F code and It worked fine on my desktop Access database), I get the run time error '3024' -it can't find my database. The debug procedure highlights in yellow the following code:
Set db = ws.OpenDatabase(DBDatabase, False, True, _
"MS Access;PWD=" & DBPassword)
Second, when I relink the tables I get the following message:
Run-time error 3291
The debug procedure highlights the code:db.Execute SQL
Then I start the database using the bypass key, I give the login and password and I get connected, though the database is very-very slow (more than 10-15'' to open just a form with 10 records). I have about 65 tables and some more forms, the whole database is no more than 30000kbs (it is empty).
I'd very much appreciate your help.

Syntax error in CREATE INDEX statement

	Richard Rost @Reply 5 years ago
Did you upload the LoginT to your SQL Server? If so, you can't use that connection string anymore with the "MS Access" in it. You need the new one that I show in the Seminar.

	Despina Karayanni OP @Reply 5 years ago
Hello Richard, For some mysterious reason I manage to connect to the SQL Server through the LoginF (I used the connectoserver code in the LoginF code, and Set db = CurrentDb, and it works fine. I also I solve the relinking tables error, by removing a table (paste errorsT) that doesn't seem to matter (probably). However my database still is very-very slow. How could I solve this problem?

	Richard Rost @Reply 5 years ago
That's usually a matter of your Internet connection. Also, make sure you're not pulling too much data over the wire at once. Start with a form that is connected to a table with ONE record in it. See if that opens quickly.

	Richard Rost @Reply 5 years ago
Are you using WinHost? If not, who is your ISP?

	Despina Karayanni OP @Reply 5 years ago
Yes, a simple form opens satisfactory. Yes, I am using WinHost.

	Despina Karayanni OP @Reply 5 years ago
I'll also transform the Yes/No Data type fields into binary. Perhaps this will speed up the complex forms (including subforms and list boxes). Thank you very much.

	Richard Rost @Reply 5 years ago
I can say that since I switched from GoDaddy to WinHost, my web site (which is built using the same techniques shown in the Seminar) and my office Access database (which connects to it) run MUCH faster. So unless you've got a real slow Internet connection, you may just need to optimize your database. The form that's loading slow, how many records are in the dataset?

Despina Karayanni OP @Reply

5 years ago

Hello,
Actually I am still testing the database with just 35 customer records and 20 orders. However, we have asked to upgrade our Internet connection speed to 200 Mbps (now we have only 30 Mbps Internet speed connection). I think that first we have to wait and see the downloading behavior of the database with the new Internet connection speed. Hope that I'll won't need to subtract any listbox or subform from my main customer with contacts form, or order form, due to slow speed. Everything else seems to work perfectly, as I've followed your guidance by the book. Thank you very much for this.

Richard Rost @Reply

5 years ago

Honestly 30 MB should be just fine unless you're pulling TONS of data over the line. As long as you use passthru queries for the big stuff and have it processed on the server, sending only small bits of data, you should be OK. At my last house, I lived out in a rural area and I only had a 2 MB DSL connection. My connection to the online database ran OK. So... I dunno. It might be more about optimizing your datasets.

	Despina Karayanni OP @Reply 5 years ago
How could I do this? Is there a seminar for this staff (database optimization)? Also I am waiting for the Access SQL Server Lessons part 2.

	Richard Rost @Reply 5 years ago
I'll be putting together a more comprehensive SQL Server course series designed to teach optimizations like this. In the mean time, just try to take any large queries and simplify them - use PassThru queries so that the server processes the information and then only sends over the wire the few records that you need.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access SQL Server Lessons.

Next Unseen

New Feature: Comment Live View