Attempting to use alternate database

[Jeff]

I am trying to set my Access project to be able to use a different
database so that when my users report problems (bugs!) I can just
switch the application to use a test database. I have everything
working fine with selecting/opening the new database. Problem is on
comboboxs. When I drop down the box I see the data from the table that
is in the project and not my alternate. If a add a record to that
table using my add function, it adds it to the alternate like it is
supposed to. My row source for the combobox was created using the
query builder. I tried to modify it to point it to the current active
database but get syntax errors.
Here is the current row source

SELECT SalesReps.Sales_Rep_Name FROM SalesReps;

I think I need to include IN in this sql stmt.

SELECT SalesReps.Sales_Rep_Name FROM SalesReps IN <database name>;

I can't seem to get the correct syntax for it to work.

 

[Tom Wickerath]

Try adding single quotes around <database name> (without the angle brackets). The result I get
when testing your SQL statement in a new query (.mdb file) is shown below. In query design view,
click on View > Properties. One of the properties is called Source Database. I entered:

C:\My Documents\MyDB.MDB in place of the default, which is shown as: (current)

into this property. The resulting SQL statement, when I view the SQL is:

SELECT SalesReps.Sales_Rep_Name AS Expr1
FROM SalesReps IN 'C:\My Documents\MyDB.MDB';

This is what leads me to suggest the single quotes. Just a guess on my part.

Tom
_____________________________________


I am trying to set my Access project to be able to use a different
database so that when my users report problems (bugs!) I can just
switch the application to use a test database. I have everything
working fine with selecting/opening the new database. Problem is on
comboboxs. When I drop down the box I see the data from the table that
is in the project and not my alternate. If a add a record to that
table using my add function, it adds it to the alternate like it is
supposed to. My row source for the combobox was created using the
query builder. I tried to modify it to point it to the current active
database but get syntax errors.
Here is the current row source

SELECT SalesReps.Sales_Rep_Name FROM SalesReps;

I think I need to include IN in this sql stmt.

SELECT SalesReps.Sales_Rep_Name FROM SalesReps IN <database name>;

I can't seem to get the correct syntax for it to work.

 

[Jeff]

Try adding single quotes around <database name> (without the angle brackets). The result I get
when testing your SQL statement in a new query (.mdb file) is shown below. In query design view,
click on View > Properties. One of the properties is called Source Database. I entered:

C:\My Documents\MyDB.MDB in place of the default, which is shown as: (current)

into this property. The resulting SQL statement, when I view the SQL is:

SELECT SalesReps.Sales_Rep_Name AS Expr1
FROM SalesReps IN 'C:\My Documents\MyDB.MDB';

This is what leads me to suggest the single quotes. Just a guess on my part.

Tom
_____________________________________


I am trying to set my Access project to be able to use a different
database so that when my users report problems (bugs!) I can just
switch the application to use a test database. I have everything
working fine with selecting/opening the new database. Problem is on
comboboxs. When I drop down the box I see the data from the table that
is in the project and not my alternate. If a add a record to that
table using my add function, it adds it to the alternate like it is
supposed to. My row source for the combobox was created using the
query builder. I tried to modify it to point it to the current active
database but get syntax errors.
Here is the current row source

SELECT SalesReps.Sales_Rep_Name FROM SalesReps;

I think I need to include IN in this sql stmt.

SELECT SalesReps.Sales_Rep_Name FROM SalesReps IN <database name>;

I can't seem to get the correct syntax for it to work.

Tom,

Thank you for your response. I tried your suggestion with the single
quotes around a hard coded path and database name and it works fine.
Problem is, I am using a text file to supply the database path and
name and need the SQL statment to read a string value that has been
loaded with that value. Does anyone know of a method to get an SQL to
use a variable in place of the database name or an I barking up the
wrong tree in trying to be able to easily switch between database
sources.

Thanks.

 

[Tom Wickerath]

Hi Jeff,

You will need to use VBA code to build your SQL statement. You could use code similar to the
following, to adjust the SQL statement after reading the path (string value) into a variable
(strSourceDatabase in the example below):

Dim strSourceDatabase as String
Dim strSQL As String

strSourceDatabase = {Load database path and name into this variable from your text file}

strSQL = "SELECT Sales_Rep_Name FROM SalesReps " _
& "IN '" & strSourceDatabase _
& "' ORDER BY Sales_Rep_Name;"

Me.cboSelectDatabase.RowSource = strSQL
Me.cboSelectDatabase.Requery


Your SQL statement brings up a couple of questions to me. First, it appears as if you are
storing the sales rep. names in one field. This is considered non-atomic data, since it is not
being stored in its simplest form. It is usually better to store first and last names in
separate fields. They can always be joined together (concatenated) later on. Second, I don't
see that you are selecting the sales rep's primary key field in your SQL statement. Are you
using the entire rep's names to relate records between the SalesReps table and a related table?
It would be better to include a long integer autonumber primary key in the combo box's row source
as a hidden bound column, and then store this number in the foreign key field of the related
field.

Tom
_________________________________________


Tom,

Thank you for your response. I tried your suggestion with the single
quotes around a hard coded path and database name and it works fine.
Problem is, I am using a text file to supply the database path and
name and need the SQL statement to read a string value that has been
loaded with that value. Does anyone know of a method to get an SQL to
use a variable in place of the database name or an I barking up the
wrong tree in trying to be able to easily switch between database
sources.

Thanks.
_________________________________________


Try adding single quotes around <database name> (without the angle brackets). The result I get
when testing your SQL statement in a new query (.mdb file) is shown below. In query design view,
click on View > Properties. One of the properties is called Source Database. I entered:

C:\My Documents\MyDB.MDB in place of the default, which is shown as: (current)

into this property. The resulting SQL statement, when I view the SQL is:

SELECT SalesReps.Sales_Rep_Name AS Expr1
FROM SalesReps IN 'C:\My Documents\MyDB.MDB';

This is what leads me to suggest the single quotes. Just a guess on my part.

Tom

_________________________________________


I am trying to set my Access project to be able to use a different
database so that when my users report problems (bugs!) I can just
switch the application to use a test database. I have everything
working fine with selecting/opening the new database. Problem is on
comboboxs. When I drop down the box I see the data from the table that
is in the project and not my alternate. If a add a record to that
table using my add function, it adds it to the alternate like it is
supposed to. My row source for the combobox was created using the
query builder. I tried to modify it to point it to the current active
database but get syntax errors.
Here is the current row source

SELECT SalesReps.Sales_Rep_Name FROM SalesReps;

I think I need to include IN in this sql stmt.

SELECT SalesReps.Sales_Rep_Name FROM SalesReps IN <database name>;

I can't seem to get the correct syntax for it to work.

 

[Jeff]

Tom,

Thanks again for your response. I had tried using the rowsource and
requery in my VBA code but had the same trouble with the database
variable string. I followed your suggestion and saw that my problem
was that I was not using the single/double quotes properly. Once I got
that straightened out it worked fine.

Originally I had designed the table with an sales rep number as the
key but the user didn't like having to keep track of number. He just
wanted the name. I didn't think of hiding it. So, I just made the
sales rep name the primary key knowing this was not going to be a
large application.

Thanks again for your help.
Jeff