Controlling SQL tables with access

[Spidy6123]

Hi guys,

So I came accross this piece of code and it seems to work fine when I want to import or just view records via access..

Does anyone know if it's possible to actually have full read and write control of the tables? So that I can have forms actually running off of them?

Public Function SQLServerLinkedTable()
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String

' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
               "Driver={SQL Server};" & _
               "Server={172.23.165.67};" & _
               "Database=BA;" & _
               "Trusted_Connection=NO;" & _
               "Uid=baadmin;" & _
               "Pwd=redarmy#1;"
               
' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

With oTable
    .Name = "mymatch"
    Set .ParentCatalog = oCat
    .Properties("Jet OLEDB:Create Link") = True
    .Properties("Jet OLEDB:Remote Table Name") = "credit_match"
    .Properties("Jet OLEDB:Cache Link Name/Password") = True
    .Properties("Jet OLEDB:Link Provider String") = sConnString
End With

' Add Table object to database
oCat.Tables.Append oTable
oCat.Tables.Refresh
Set oCat = Nothing

End Function

 

[genomon]

Why not just link the SQL tables into Access via ODBC and use them (or a query derived from them) as the form's recordsource?

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[Spidy6123]

I don't want to have the client enter the sql password everytime.. hence the code..

 

[MikeAuz1979]

If that's the only problem couldn't you just tick 'Save password' when linking the tables? Or if you prefer you can use a pass through query in native (Sql Server) sql and click on View\Properties click on ODBC Connect Str, click on the 3 dot box, select your datasource and then click yes to save password.

 

[Spidy6123]

Actually the password doesn't actually save.. and I also want to away from anything that I have to configure on the clients machine.. this is going to be use by about 100 users..

as for pass through queries, they do not allow write.. if I'm not mistaken..

 

[Spidy6123]

FYI problem has been solved.

In order to write back to the sql table.. the table requires a primary key.. so all I did is add a autonumber primary key to the table on the sql level and then ran my script again with the new table fields..

Now I have access to the sql server with connection string embedded in the table and no password required. This database is now ready for distribution.

thanks for your help