Access Project and SQL 1

[Guest_imported]

Here goes. When you are connected to a SQL database, it is possible to connect or link to an Access database at the same time?

Here's what I am doing for now. When I click a combo box on a form, it is loading data from a local access database to the project by the trasferdatabase method. I suppose you are wondering why I am doing this instead of just pulling lookup values from the server? Reason is that we are making a mobile client solution to transmit data over a CD/PD (cellular freq). I do not want to tie up the freq with the retrieval of lookup information from the server. I do simply put data in the controls and that accounts for most of the lookup data that is needed.

PROBLEM is that you can only put some many values in a combo box for lookup, unless you are using a table (like I would prefer to do) If you are typing them in, you are limited and I'm looking to have 1300 values in two different combo boxes, both for accuracy and speed.

Can I populate a combo box in code? I have tried and it hasn't worked very well. It would be great to link to a local access DB for lookup. Looking for the right solution...........Terry

Thanks for any ideas and I AM OPEN TO SUGGESTIONS

 

[demoman]

You CAN have Access and SQL server linked concurrently.

Could you have your control tables stored as ASCII files and use them to populate your controls? I have done this successfully for remote job-site locations. I did not have 1300 rows; probably closer to 200-300. They load locally in less than 5 seconds.

 

[Guest_imported]

How to link to both?

How to use the ASCII file? Both sound good.

Thanks

 

[demoman]

Below is an example of how to load a combo from an ASCII file. The file has one field, and uses the combo's index value for a reference. You can load more than one field.
Of course, you have to create the ASCII file and there are a few way of doing this.

public sub Load_Combo()

Dim strDescr as string
dim lngIndex as long

lngIndex = 1

Open "FILE" For Input As #1 ' Open file for input

Do While Not EOF(1) ' Loop until end of file.

Input #1, strDescr' Read data into variables.

with cboMyCombo

.additem strDescr,lngIndex

end with

end sub

lngIndex = lngIndex + 1

Loop
Close #1 ' Close file.

As far as the multiple connections, are you using DSN's or what?

 

[Guest_imported]

Thanks for the info and I will give that a try right now.

As for the connection, it is IP over CD/PD, kinda slow,thus the reason for moving all the crap and having to come up with alternative methods.

Thanks again

Terry