hi Marybeth,
I find using DSN files to be a good way to connect, especially for beginners. DSN stands for Data Source Name.
Make a file with NotePad or another text editor with the following (not the dashed lines):
-------------------------------------------
[ODBC]
DRIVER=SQL SERVER
UID=MyUserID
DATABASE=MyDatabaseName
SERVER=MyServerName
PASSWORD=MyPassword
-------------------------------------------
WHERE
DRIVER=SQL SERVER -- this is the basic driver for SQL Server. Others are faster, but this one should work if you don't know the exact name of another driver.
UID=your user ID.
DATABASE=the name of the database you want to connect to.
SERVER=the name of the server where the database is.
PASSWORD is not necessary if you are using Windows Authentication. If not, make MyPassword whatever your password is
Save this file. Call it something like MyDatabase.dsn where MyDatabase lets you know what database it is set up for.
After you close the file, if you used NotePad to make it, you will need to RENAME it to make the extension = dsn since Notepad always wants to use txt.
Now that you have a DSN file, you can use it to link to tables in that database from Access.
From the ribbon, choose: EXTERNAL Data.
Then click the ODBC Database icon� in the Import & Link group.
You have option buttons to Import or Link -- choose "Link to the data by creating a linked table"
OK
On the File Data Source tab, paste or type the complete path\file for the dsn in the box called "DSN Name". For instance:
C:\myPath\dsn\MyDatabase.dsn
OK
In the SQL Server Login dialog box, if you are using Windows* authentication, check the "Use Trusted Connection" box
OK
You should now see a list of tables you can connect to. Select which tables you want.
You may want to check the "Save Password" box.
OK
* also called Windows/Active Directory
have an awesome day,
crystal
Hi Marybeth,
Can you tell us what rights/roles you have in the SQL database? Are you using an ODBC/DSN with your connection?
Are you using SQL authentication with a login and password or Windows/Active Directory authentication?
Regards,
Duane
Crystal,
�
I did try and link from Access and I couldn�t �see it� to select.� The migration isn�t working now either.� I really need some guidance.
�
Thanks in advance.
�
Marybeth
�
Wed Mar�28,�2018 1:10�pm (PDT) .. Posted by:
hi Marybeth,
>"I couldn't see in the access FE"
to have the table show in the navigation pane, you need to link to it
from Access.� Did you do that? Or try that?
have an awesome day,
crystal
On 3/28/18 2:43 PM, Marybeth S Tomka marybeth.tomka@austin.utexas.edu
[MS_Access_Professionals] wrote:
>
>
> Afternoon,
>
> Please forgive my ignorance in advance.� I have just split a database
> into an SQL BE and an access FE in order to have multiple users in the
> database.� We used a migration tool to do the split and with a few
> hiccups I can use it like I used to..� The other day I created a new
> table in the SQL BE and found that I couldn�t see in the access FE.
> Since I don�t have full rights to the server that the database lives
> on I am not sure if I don�t have rights to alter the connection or if
> I am attempting to make the connection incorrectly.� So I tried
> creating the table in the Access FE and migrating it to the BE.� Two
> of the four tables I wanted to add migrated just fine, the other two
> did not � �target does not exist� was not very helpful.� So my
> questions are since neither routine worked correctly, which would be
> the best way to add tables and second do you all have any ideas what
> went wrong with the two ways?
>
> Thank you in advance.
>
> Marybeth
�
�
