My Form RecordSource:

[pedie]

Hi, i want to set Form RecordSource to Different Database, is that possible, how can i do this?

can i open some connection and make/set RecordSource?
Please advice.

Thanks & Regards
Pedie

 

[Norie]

Pedie

Why not create a link to the table/query in the other database and use that as the record source?

You would be able to do that without any code.

If you did use a connection I think you would have to write code to populate the form, code to navigate the recordset, code to update/delete/add records etc.

Probably some other things too, eg filtering, searching.

 

[Norie]

Seems I was wrong.

http://support.microsoft.com/kb/281998

Still think linking might be a better idea.

 

[pedie]

Norie, thank you soo much for the link...
I'll do try it out and get back...

Thanks again

 

[Norie]

Pedie

Why don't you try the link idea first?

You seem to be intent on using code for things that Access has built-in.

That might not be the best idea especially if you are unfamiliar with Access.

 

[pedie]

Hi again Norie, i tried to use this code but can't make it work...

I know 'm asking too many things now but i need help esp. here.

[/FONT]
[FONT=Courier New]Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Use the ADO connection that Access uses
   Set cn = CurrentProject.AccessConnection

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM Customers"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open 
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs

   Set rs = Nothing
   Set cn = Nothing

I dont know where to enter code like this which has file path, password etc...

[/FONT]
[FONT=Courier New]Dim strCNXN As String
Dim CNXN As New ADODB.Connection
Dim db As String
db = "E:\Personal_Files\Access\MAINDB.accdb"
Set CNXN = New ADODB.Connection
strCNXN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=E:\Personal_Files\Access\MAINDB.accdb;Uid=Admin;Pwd=123;"
CNXN.Open strCNXN

 

[pedie]

Norie, I could use the linking method, however when data gets deleted in this front end table the maindatabase will lose the data too that i do not want to happen and when i try to use Append/Select Query or even code to transfer my data it wont work because it has multi-value field [attachments] in it.
After everything has failed me...i'm trying to use the form to link to main database table and directly update there is possible...

I tried the below code and everything went blank when the form loaded...

Please please advice, anyone!

[/FONT]
[FONT=Courier New]DoCmd.GoToRecord , "", acNewRec[/FONT]
[FONT=Courier New]Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strCNXN As String
Dim CNXN As New ADODB.Connection
Dim db As String
         
db = "E:\Personal_Files\Access\MAINDB.accdb"
Set CNXN = New ADODB.Connection
strCNXN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=E:\Personal_Files\Access\MAINDB.accdb;Uid=Admin;Pwd=123;"
CNXN.Open strCNXN[/FONT]

[FONT=Courier New]   'Use the ADO connection that Access uses
   Set cn = CNXN[/FONT]
[FONT=Courier New]   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "SELECT * FROM OKOKTable1"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing

 

[Norie]

Pedie

See my original post, if that's what you want to do then you'll need to write code for all the built-in functionality that Access offers.

Why don't you want data deleted from the source?

What do you mean by front-end table?

Have you split a database into front-end/back-end?

If you have you shouldn't have any tables in the front-end, and you should have links to all the tables in the back-end.

That's the basic idea of back-end/front-end.

 

[Norie]

Pedie

PS Why multivalue fields?

I'm sure you're familiar with merged cells in Excel and how most people recommend avoiding them if at all possible.

Well some people, myself included, kind of feel the same way about multi-value fields.

Nice idea but not too practical.

 

[pedie]

[/FONT]
[FONT=Courier New]See my original post, if that's what you want to do then you'll need to write code for all the built-in functionality that Access offers.[/FONT]
[FONT=Courier New]

this would great but 'm not capable of this..

[/FONT]
[FONT=Courier New]Why don't you want data deleted from the source?[/FONT]
[FONT=Courier New]

'm not sure what this means, Norie.

[/FONT]
[FONT=Courier New]What do you mean by front-end table?[/FONT]
[FONT=Courier New]

I have front end with table and a form. This form is used to update data with few fields data and attachments into my table...
'm doing this because the attachments wont goto table though code but docmd.save command...

[/FONT]
[FONT=Courier New]Have you split a database into front-end/back-end?[/FONT]
[FONT=Courier New]

'n not sure what "split a database" means but 've one table in front end where different users from different computer and location would update data into which, later is transfered to the main database table which same structure...

[/FONT]
[FONT=Courier New]If you have you shouldn't have any tables in the front-end, and you should have links to all the tables in the back-end.[/FONT]
[FONT=Courier New]

Hmmm...this linking system/method...
If i link table from main database into front end....and i update data from the form, will the data still be available when the form and front end is closed in main database?

[/FONT]
[FONT=Courier New]Why multivalue fields?[/FONT]
[FONT=Courier New]

Just because it is required by business....to attacheed some small files esp. txt, msg item for some tracking purpose...



Thanks again....for always being there...